Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Delete rows autofilter range problem
37 views
Skip to first unread message
deutz
May 22, 2012, 11:21:35 PM5/22/12
to
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
--
deutz
James Ravenswood
May 23, 2012, 8:48:34 AM5/23/12
to
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
May 23, 2012, 11:19:42 AM5/23/12
to
deutz presented the following explanation :
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
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
May 23, 2012, 3:24:35 PM5/23/12
to
You cannot Offset(1, 0) from row 65536
Gord
On Wed, 23 May 2012 03:21:35 +0000, deutz
<deutz....@excelbanter.com> 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
>End With
Gord
On Wed, 23 May 2012 03:21:35 +0000, deutz
<deutz....@excelbanter.com> 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.Delete
>End With
deutz
May 23, 2012, 7:57:24 PM5/23/12
to
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?
'Gord Dibben[_2_ Wrote:
> ;1602062']You cannot Offset(1, 0) from row 65536
> -
--
deutz
deutz
May 23, 2012, 11:49:28 PM5/23/12
to
I finally managed to get it working by adapting someone's code ...
Code:
--------------------
rRange.AutoFilter Field:=mLastCol + 1, Criteria1:="X"
Set rng = ActiveSheet.Range(Cells(StartRow+1, StartCol), Cells(EndRow, EndCol + 1))
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
--------------------
0 new messages