Originally Posted by deutz

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