how to delete all hidden rows?

[egghi]

Hi,

I used autofilter first, and then I would like to delete all hidden rows (the rows that do not meet the criteria.

My coded did not work the way I wanted though:

Columns("C:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=HA*", Operator:=xlAnd
Range.SpecialCells(xlCellTypeHidden).EntireRow.Delete

Any advice?

Thanks in advance!

 

[AnotherHiggins]

You seem to have made up "xlCellTypeHidden". It would be nice if it existed, but it doesn't.

So you have two options:

1)
Reverse your criteria so you hide what you want to keep and delete xlCellTypeVisible

2)
copy the visible cells to another sheet and delete the old sheet. Something like this:

OldSheetName = ActiveSheet.Name
Sheets.Add
NewSheetName = ActiveSheet.Name
Sheets(OldSheetName).Select
Cells.SpecialCells(xlCellTypeVisible).Copy
Sheets(NewSheetName).Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Sheets(OldSheetName).Delete
Application.DisplayAlerts = True
Sheets(NewSheetName).Name = OldSheetName

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

I usually like to add error handling for the SpecialCells, as it will fail if none are available or there is too many for it to handle...

    Dim ws As Worksheet, wsOld As Worksheet, NewSheetName As String
    Set wsOld = ActiveSheet
    Set ws = ActiveWorkbook.Sheets.Add
    NewSheetName = wsOld.Name
    Application.DisplayAlerts = False
    On Error Resume Next
    wsOld.Cells.SpecialCells(xlCellTypeVisible).Copy ws.Cells(1, 1)
    If Err <> 0 Then
        ws.Delete
        MsgBox "There were no cells to transfer!", vbInformation, "ERROR!"
        Exit Sub
    End If
    wsOld.Delete
    ws.Name = NewSheetName
    MsgBox "Cells transfered!", vbInformation, "Complete!"
    Application.DisplayAlerts = True

(Hope you don't mind John.)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci