X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Excel 2007 8192 Restriction, Office 2007    
post May 18 2017, 11:17 AM

Posts: 478
Joined: 18-October 06

Is it still correct that Excel 2007 cannot select more than 8192 areas to copy and paste in VBA? I have a spreadsheet that filters to twice that and I would like to copy and past the visible cells only. The code I have counts first and returns the message it cannot be done. Is that correct?

It works fine when I use the macro recorder because it does not check the count.

This is that portion of the code I have;

CCount = 0
    On Error Resume Next
    CCount = My_Range2.Columns(1).SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count
    On Error GoTo 0
    If CCount = 0 Then
        MsgBox "There are more than 8192 areas:" _
             & vbNewLine & "It is not possible to copy the visible data." _
             & vbNewLine & "Tip: Sort your data before you use this macro.", _
               vbOKOnly, "Copy to worksheet"
        'Add a new Worksheet
        Set WSNew = Worksheets.Add(After:=Sheets(ActiveSheet.Index))
   End If
        On Error Resume Next

Thank you

I'm just a beginner...take pity on me.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    22nd May 2017 - 08:10 PM