UtterAccess.com
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    
 
   
KathCobb
post May 18 2017, 11:17 AM
Post#1



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;

CODE
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"
    Else
        'Add a new Worksheet
        Set WSNew = Worksheets.Add(After:=Sheets(ActiveSheet.Index))
      
   End If
  
        On Error Resume Next


Thank you
Kathy

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
ranman256
post Jun 14 2017, 09:27 AM
Post#2



Posts: 775
Joined: 25-April 14



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


Utter Access VIP
Posts: 6,007
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.

--------------------
Dan

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    22nd November 2017 - 09:51 AM