Limitations of .SpecialCells Method

[Goshute]

I'm using autofilter to hide cells which don't meet a criteria and then deleting all visible rows, as this should be faster than looping each row. The data sets are fairly large, up to 130,000 rows, and up to 20% of the rows being deleted. Actual code is:

Code: Select all

  rngHeaderRow.AutoFilter Field:=lngUnitCol, Criteria1:="0", Field:=lngPaidCol, Criteria1:="$0.00", VisibleDropDown:=False
  On Error Resume Next ' if no cells in the range are visible, next line will error out
  rngAllData.SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
  rngAllData.AutoFilter = False

However I'm concerned about http://support.microsoft.com/kb/832293/en-us" onclick="window.open(this.href);return false;. It's not clear to me if this means that my source data range can't have more than 8,192 areas (not a problem - it will always be a single continguous range), or if it means that if there are more than 8,192 areas returned by the SpecialCells method (definitely a problem, most of the time there'll be more than 8,192 rows)...anyone have any guidance on this?

[HansV]

The latter - the problem occurs if SpecialCells returns a range consisting of more than 8,192 areas.

As a workaround, you could sort the data on the relevant columns before filtering; the visible rows will then form one contiguous area (or a few areas).
If sorting the data is undesirable, you could first add a column and use it to number the rows sequentially. Then sort on the relevant columns, filter, and delete the visible rows. Remove the filter, sort on the sequence number, and finally remove or clear the auxiliary column.

[Goshute]

Good workaround idea, Hans. I was already thinking of sorting, then looping rngAllData to find the first and last rows meeting the criteria, then deleting that range, but a hybrid of .Sort Method and .SpecialCells Method may be the easiest to code. And I'm not finding the current code to be blazing fast, perhaps this will speed it up.

[Goshute]

Holy smokes! That is fast! Apparently deleting multiple rows runs much faster whenever the rows are contiguous.