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: 480
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
post Jun 14 2017, 09:27 AM

Posts: 856
Joined: 25-April 14

when you say 'areas', do you mean cells?
Go to the top of the page
post Jun 15 2017, 11:18 AM

Utter Access VIP
Posts: 6,125
Joined: 22-June 04
From: North Carolina

I think what he means is "chunks."

Suppose you have a 10-row data area and you apply a filter.
Rows A2:A3 are visible
Rows A4:A8 are hidden
Rows A9:A11 are visible

This would comprise three "chunks."

I guess I mean the number of discontiguous areas.


One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 06:31 PM