how to select rows after autofilter

[John Keith]

The following commands make a part of macro I'm trying to complete

Selection.AutoFilter Field:=24, Criteria1:="="
Rows("10:1186").Select
Selection.Delete Shift:=xlUp

The first line specifies an autofilter and a criteria that generates
an output typical of the autofilter function.

This next two commands selects and deletes all the rows (and then some
for good measure, hence the 1186) that need to be deleted. These lines
were generated during a "record macro", hence the explicit 10:1186.

Now it is expected that the starting row to delete will not always be
row 10 due to the variability of the content of the rows before the
filter is applied (well duh!)

So, my question is, how is the filter output more generically selected
so that all any rows from the filter output result will be deleted?

Thanks for any suggestions!

John Keith
(e-mail address removed)

 

[leungkong]

I think you can use looping to delete the row one by one.

Sub DeleteBlank()
Application.ScreenUpdating = False
Dim i As integer, lastrow As integer
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
With Worksheets("Sheet1").Range("X" & i)
If .Value = "" Then
.EntireRow.Delete
End If
End With
Next
End Sub

 

[Nigel]

Create a range object and assign the filtered rows to that, in this case it
is deleted.

Dim rng2 as Range, wShData as Worksheet
Set wShData = ActiveWorkbook.Worksheets("Sheet1")

With wShData.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then rng2.EntireRow.Delete
End With

 

[John Keith]

On Fri, 29 Aug 2008 00:06:00 -0700, leungkong

Leungkong,

Thank you for your reply.

I need to ask some questions so I can understand all the details of
your suggestion.

Was this Sub intended to operate on the filtered output or are you
suggesting replacing the autofilter command completely with this Sub?

I assume I could put this code in line with my existing code rather
than having it be a separate Sub?

I think you can use looping to delete the row one by one.

Sub DeleteBlank()
Application.ScreenUpdating = False
Dim i As integer, lastrow As integer
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 2 Step -1
With Worksheets("Sheet1").Range("X" & i)
If .Value = "" Then
.EntireRow.Delete
End If
End With
Next
End Sub

John Keith
(e-mail address removed)

 

[John Keith]

Nigel,

Thank you also for your suggestion.

Like the other response to my inquiry I need to better understand how
this works.

I assume the Dim and Set commands need to be located early in my
macro?

Then the With loop lines are located after the filter command?

Sometimes usenet messages have lines broken in unintended locations.
Was the Set rng2... line and the next line .SpecialCells.....
supposed to be one line?

I have no idea what is being done in the set rng2 row. Is there a
reference you can point me to in order to get an understanding of what
is being done?

I assume the .SpecialCells(xlCellTypeVisible) is identifying the
visible data that remains after the filter command?

Create a range object and assign the filtered rows to that, in this case it
is deleted.

Dim rng2 as Range, wShData as Worksheet
Set wShData = ActiveWorkbook.Worksheets("Sheet1")

With wShData.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then rng2.EntireRow.Delete
End With

John Keith
(e-mail address removed)

 

[John Keith]

I think you can use looping to delete the row one by one.

Leungkong,

Thank you for your recommendation. This weekend I finally implemented
your suggestion and it worked great. For the number of rows I need to
delete the looping does not take significant time.


John Keith
(e-mail address removed)

 

[John Keith]

Create a range object and assign the filtered rows to that, in this case it
is deleted.

Nigel,

I also tried your suggestion this weekend and it worked fine, but I
still don't have a good understanding of how it works. Thanks for the
suggestion.


John Keith
(e-mail address removed)