VBA To Extract Data From Multiple Tables Based On Critera

**aaronb** · 06-01-2013, 02:46 AM

Update: Kind of hacky, but I managed to limit the find/replace to the specific worksheet by doing this before the find/replaces:

Code:

Set Dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

Now, just need help on the deleting part, thanks

This also works for deleting if there are rows with blank values in Column 2 of the table, but throws an error if no blanks were found. Guess it just needs error checking?

Code:

Dim RngBlank As Range
    With ActiveSheet.ListObjects("Orig").Range
        .AutoFilter Field:=2, Criteria1:="="
        Set RngBlank = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        .AutoFilter
        RngBlank.Delete
    End With

Edit: Looks like I got it. If there's a better way to do it, please let me know.

Code:

With ActiveSheet.ListObjects("Orig")

        .Range.AutoFilter Field:=2, Criteria1:="="
        
        If .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
            .DataBodyRange.EntireRow.Delete
            .Range.AutoFilter Field:=2
        Else
            .Range.AutoFilter Field:=2
        End If
        
End With