You cannot Offset(1, 0) from row 65536


Gord

On Wed, 23 May 2012 03:21:35 +0000, deutz
wrote:


Hi and thanks in advance,

I am using Excel 2003

I have a delete rows macro that tests whether there is data in one or
more user defined columns on the sheet and if not, places an "X" in the
first blank column to the right of the data. I then apply an autofilter
to pick up all rows with an "X" and then would like to delete all the
filtered rows except the first one in the range which is a header row.
The first row of data may be any row on the sheet as defined by the user
via a userform.

The code below works fine if there is no data in row 65,536, the last
row on the worksheet. If there is a value in the last row then the macro
does not delete the filtered rows as required. When the user defines a
range that includes all rows on the sheet, from 1 to 65,536, I think
Excel changes the range address to a column reference eg. "A : D"
instead of the full address "A1 : D65536" so that when you apply the
offset method it offsets to the wrong row. Any ideas?

With rRange
AutoFilter Field:=mLastCol + 1, Criteria1:="X"
Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
End With

Thanks for your suggestions. Yes, that makes sense now that you put it like that ... there is no row 65537 to offset to. I did intially have some code that did not use a filter but looped thru the rows and deleted from the bottom up ... however, this proved a bit too slow, hence the use of a filter. Is there another way I can delete all filtered rows that will not crash on row 65536?