Selection is too large

[martinee]

I am trying to run some code that will delete all the empty cells in column A. The code I am currently using is:

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I know for a fact that several thousand lines in column A are not blank, however, after this code runs, my entire worksheet is deleted. If I try to manually delete the blank rows using the same method (i.e. I highlight column A, and the GoTo | Special | Blanks etc..) I get an error saying the selection is too large. The data range is only 48000 lines, so the blank lines must be less than that. Why would I be getting the error? Any thoughts? Thanks in advance.

 

[shades]

The limit for Selected ranges: 2,048. So, if these are non-contiguous, they may exceed the 2,048 ranges.

 

[martinee]

Good to know. Thanks.

Any reason why when done via VBA, instead of giving an error it simply selects the entire column A and deletes it instead?

 

[shades]

Don't know. I tried it on a small scale ~20 rows of incomplete data (some blanks), and it worked fine.

 

[Zack Barresse]

This works for me just fine ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> DelBlanks()
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


If you still get an error, try posting an example of your data using the HTML Maker.

 

[martinee]

I begrudgingly wrote a loop to delete it. I tried to use a more direct approach because of the large amound of data, but it doesn't seem to like so much data.

 

[shades]

Someone at MrExcel posted this a couple years ago and gives flexibility in choosing the column on which to base the deletion(basically what Zack and I already demonstrated). Works great for me:

Sub DeleteEmptyRowsMain()
'This one I picked up from Mr. Excel.com which allows user to
'choose the column by selecting it.
    Dim myColm As Range
    Set myColm = Application.InputBox("Choose column(s) to clear", Type:=8)
    On Error Resume Next
    myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[martinee]

Yes, I'm sure that code would work fine so long as, like you said, there are less than 2048 ranges. My data had about 10,000 blank lines, so I believe that was my issue. Hence the loop.

 

[KenWright]

If you have more than 8192 separate blank ranges in your usedrange in that column, then specialcells will return a single range which will be the whole column. Deleting that will delete your whole sheet. You have to test to see how many ranges are returned, and if only then go into a looping mode to break the size of the range down and then use specialcells.

 

[martinee]

Thanks Ken.

When I run code that just does:

Columns("A:A").SpecialCells(xlcelltypeBlanks).Select

The entire column is selected, just like you said it would be. Hence my blank worksheet. Thanks all.