Special Cells to Loop back

[Hafeez Esmail]

I've read (in the forums) that it is best to delete from
bottom up and I use this technique, however I'm having a
bit of trouble setting up a for loop for one of my lists.

I have a filtered list and have been looping through the
code (from top to bottom) by the following.

'Row one is the header row
intFrstRow = Range("A1").End(xlDown).Row
intLastRow = Range("A" & intFrstRow).End(xlDown).Row

'Create range
Range("A" & intFrstRow &, "A" & intLastRow).SpecialCells
(xlCellTypeVisible).Select

I tried setting the range the other way (intLastRow,
intFirstRow) but that didn't help.

The only method I can think of (one given by a helpful
MVP) is using a do loop to start at the end and count
backwards until hits a visible row.
Is there a faster method than this?

Thanks
Hafeez Esmail

 

[J.E. McGimpsey]

One way:

This sets up a range of rows to delete, then deletes all the
unwanted lines at once:


Dim rDelete As Range
Dim rCell As Range
On Error Resume Next 'in case no visible cells
For Each rCell In Range("A2:A" & Range("A" & _
Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
If rCell.Value = 1 Then 'or whatever criteria you use
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
On Error GoTo 0
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete

 

[Tom Ogilvy]

Dim rng as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1)
' exclude the header row
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.SpecialCells(xlvisible).Select

 

[Hafeez Esmail]

Thanks guys
You're the best!

-----Original Message-----
Dim rng as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1)
' exclude the header row
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.SpecialCells(xlvisible).Select

--
Regards,
Tom Ogilvy




.