How to use VBA to select all unhide rows in a table?

[Azuki]

I have a table. I want to write macro to filter a column. The result will hide some rows. I want to delete all unhide rows. How can I do that?

 

[Andrew Poulsom]

Does this work for you?

ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Select

 

[Azuki]

It doesn't work. I get a Object varible or With block variable not set.

Does this work for you?

ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Select

 

[Andrew Poulsom]

Have you AutoFiltered the active sheet?

 

[orangefury90]

Hello,

Maybe I can answer part of your question.

To delete rows that are hidden try:

Sub delete_hidden_rows()
Dim i As Integer

For i= 1 To 100 'Note, this will look at only rows 1 to 100

If ThisWorkbook.Sheets("Sheet1").Rows(i).Hidden = True Then​

ThisWorkbook.Sheets("Sheet1").Rows(i).Delete Shift:=xlUp​

i = i - 1 'Must do this in case there are 2 hidden rows in a row.​

End If​

Next i

End Sub

 

[orangefury90]

I re-read your question and my answer probably won't help do exactly what you want! sorry.

 

[Azuki]

Yes, the entire codes as follow:

Sub Macro1()
    
    ActiveSheet.ListObjects("Project_List").Range.AutoFilter Field:=2, Criteria1 _
        :="=RDD", Operator:=xlOr, Criteria2:="=SWCC"


    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Select
End Sub

Basically, I want to delete rows which field 2 is "RDD" or "SWCC".

Have you AutoFiltered the active sheet?

 

[Azuki]

Thanks all who have helped. I manage to get it.

Sub Macro1()
'
' Macro1 Macro
'


'
    Dim colNoProjCluster
    Range("Project_List[[#Headers],[Project Cluster]]").Offset(0, 1).Select
    ActiveSheet.ListObjects("Project_List").Range.AutoFilter Field:=2, Criteria1 _
        :="=RDD", Operator:=xlOr, Criteria2:="=SWCC"
    Range("Project_List").SpecialCells(xlCellTypeVisible).EntireRow.Delete
        
End Sub

 

[Azuki]

Sorry, I don't know why this didn't work. But I thought I manage to get it to work just now.

 

[Andrew Poulsom]

Does this work for you?

Sub Macro1()
    Dim Rng As Range
    With ActiveSheet.ListObjects("Project_List")
        .Range.AutoFilter Field:=2, Criteria1 _
            :="=RDD", Operator:=xlOr, Criteria2:="=SWCC"
        Set Rng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        .Range.AutoFilter
        Rng.Delete
    End With
End Sub