Excel 2003 (VBA) fails to delete selected rows

[Dennis]

Using XL 2003
256MB Ram
2.1 GH Intel
80G HD 75% free
XL File size = 2 MB

Using AutoFilter via VBA, I have selected 10,000 out of 25,000 records.

I wish to delete all records with a blank in column A. In this case, 10,000 records.

The VBA code that I was using is:

Range("A1", Selection.SpecialCells(xlCellTypeLastCell)).Select
Selection.AutoFilter Field:=1, _ Criteria1:="="
Range("A2", Selection.SpecialCells(xlCellTypeLastCell)).Select
Selection.EntireRow.Delete

The deletions using VBA gave inconsistent results; sometimes all records; other
times, all but about ten records were deleted. (NOTE: If I limited the records to
about 1000, the VBA code worked perfectly and consistently!)

Therefore, I began to test things manually. This is what I found out:
When I attempted to delete Auto filtered & Selected rows manually,
XL gave the following error message:

"Microsoft Office Excel cannot create or use the data range because it is too
complex"
"Try one or more of the following:
Use data that can be selected in one contiguous rectangle
Use data from the same sheet"

What is complex about selecting rows for blanks in column A and attempting to delete
them?

NOTE: the workbook has only one worksheet!

TIA Dennis

 

[Ken Wright]

Try this:-

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[Dennis]

Ken,

Thanks for your reply.

The issue continues. If I limit, the records, this attempt to about 5,000, all works
as expected - with your code or mine.

Every time I attempt the full 25000 records, the VBA bombs and deletes all records!

Possibly a workaround for a RAM memory issue? (at 256MB should not be a problem?)

What is the easy way to delete 5000 records at a time until all selected (via
auto-filter) are deleted?

 

[Tom Ogilvy]

I believe specialcells is limited to 8192 distinct ranges.

In xl95 and earlier, worksheets had only 16384 rows - so the worst case
would be every other row - thus 8192 areas. This appears to be a hold over
from earlier versions.

 

[Tom Ogilvy]

Sub AAAA()
Dim rng As Range
For i = 20002 To 2 Step -5000
On Error Resume Next
Set rng = Rows(i).Resize(5000).SpecialCells(xlVisible)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
Next

End Sub

 

[Ron de Bruin]

See this Kb

http://support.microsoft.com/default.aspx?scid=kb;en-us;832293&Product=xlw

 

[Ken Wright]

I just learned something - cheers Tom.

Ron, thanks for the KB

 

[Ron de Bruin]

Hi Ken

David have some notes about it also on his site
http://www.mvps.org/dmcritchie/excel/delempty.htm#testdata

 

[Ken Wright]

Oh boy, if I had the brains of a bunny rabbit then I would have realised that,
especially given that I often use that feature to fill in the blanks in the
source data of a Pivot Table with 0s to avoid any of the automatic fields
subtotalling with COUNT instead of SUM. Occasionally when I do this, I get the
'selection too large' message, and just never dug into it, but did in fact do it
in a couple of operations instead of one. <grrrr>

Still, at least now I know *why* I was getting that error - Cheers again Tom/Ron

 

[Jim Cone]

Also, thank you to Tom and Ron.

I ran some tests using David M's test data.
It appears that SpecialCells returns the entire range if the cell limit is
exceeded.
Can anyone confirm that?

Regards,
Jim Cone
San Francisco, CA

Hi Ken
David have some notes about it also on his site
http://www.mvps.org/dmcritchie/excel/delempty.htm#testdata
Regards Ron de Bruin
http://www.rondebruin.nl

-snip-

 

[Ron de Bruin]

Yes Jim

You lost all your data

 

[Jim Cone]

Ron,

Thanks for confirming. No data lost today.
Actually, I am reviewing existing code to make sure it there are no holes.
Looks like I am Ok. The following selects all cells with something in them.
If the 8192 cell limit is breached, I get the entire range returned instead of
the portion with data.
'-----------------------
On Error Resume Next 'If no cells found then error (1004)
Set CellRange = AllCells.SpecialCells(xlCellTypeFormulas)
Set SpecialRange = AllCells.SpecialCells(xlCellTypeConstants)
On Error GoTo Error_Stopper
If CellRange Is Nothing Then 'Union method errors if a range is nothing
Set CellRange = SpecialRange
ElseIf SpecialRange Is Nothing Then
Set SpecialRange = CellRange
End If
Set GenericRange = Application.Union(CellRange, SpecialRange)
'----------------------------

Regards,
Jim Cone

Yes Jim

You lost all your data
Regards Ron de Bruin
http://www.rondebruin.nl

- snip -

 

[Ron de Bruin]

Hi Jim

If you use run this with more then 8,192 non-contiguous empty cells
you have a empty sheet Jim

Public Sub DeleteBlankRows()
On Error Resume Next 'In case there are no blank rows
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

If you have more then 8,192 the Areas count is 1

MsgBox Columns("A").SpecialCells(xlCellTypeBlanks).Areas.Count