PDA

View Full Version : How to Delete Visible Rows in VBA?



genracela
07-02-2010, 01:17 AM
I'm just wondering what's wrong with my code?

The line:
Range("A1:H999000").AutoFilter Field:=4, Criteria1:="-100"
is okay, but I'm having problem with the second line.

Thanks!

Sub Filter()
With ActiveSheet
Range("A1:H999000").AutoFilter Field:=4, Criteria1:="-100"
Columns("A").SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

End Sub

GTO
07-02-2010, 01:21 AM
Try Range("A:A")

Bob Phillips
07-02-2010, 02:13 AM
Sub Filter()
Dim rng As Range
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A1:H" & LastRow)
rng.AutoFilter Field:=4, Criteria1:="-100"
Set rng = rng.Columns("A").SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

End Sub

genracela
07-04-2010, 06:01 PM
I'm still having trouble with:

Columns("A").SpecialCells(xlCellTypeVisible):(

I tried doing it manually though(select rows, then F5 visible cells only), but it gives me this error message(pls see attch image).

Bob Phillips
07-05-2010, 12:03 AM
Can you post the workbook?