Delete rows autofilter range problem

[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 o
more user defined columns on the sheet and if not, places an "X" in th
first blank column to the right of the data. I then apply an autofilte
to pick up all rows with an "X" and then would like to delete all th
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 use
via a userform.

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

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

 

[James Ravenswood]

If you already have a macro that finds the rows to be deleted, why not delete them immediately rather than mark them and delete them later??

 

[GS]

deutz presented the following explanation :

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.Delete
End With

Why not...?

Instead of placing an 'x' in the column to the right, simply delete the
row right then and there, saving the extra step of filtering. I
recommend, though, that you start at the last row containing data and
work up from there so the deleted rows don't shift into your current
position in your loop. (Assumes you're looping through the rows of data
via 'For...' or 'For Each...')

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Gord Dibben]

You cannot Offset(1, 0) from row 65536


Gord

 

[deutz]

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

;1602062']You cannot Offset(1, 0) from row 65536


Gord

On Wed, 23 May 2012 03:21:35 +0000, 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" i the
first blank column to the right of the data. I then apply a 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 th 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 th 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.Delete
End With

 

[deutz]

I finally managed to get it working by adapting someone's code ...



Code