Convert Range vba code to ListObject vba code

[spydey]

So I have some code that I have been using for about a year. It works great!!

However, it is for a workbook where all the data is in range format.

I recently re-built the data structure from the ground up in a new workbook, and implemented tables.

It has made things much nicer and easier to reference. Formulas are easier to follow, I need only a minimum of named ranges, etc.

I went from 7 worksheets & 60+ named ranges in the old workbook, to 7 worksheets (each being its own table, so 7 tables) and 1 named range in the new workbook. It is beautiful!

But now I need to re-code my vba to work correctly with the tables.

Here is my previous code. This is just a basic snippet of it:

Private Sub Separation()


Dim rng As Range
Dim Tracking As Variant
Dim i As Long
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set rng = wb.Worksheets("Sheet1").Range("Summary")
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

Tracking = rng.Value

For i = LBound(Tracking) To UBound(Tracking)

wb.Worksheets.Copy
    
Set ws = ActiveWorkbook.Worksheets("Sheet1")
                
    With ws
        .AutoFilterMode = False
        .Rows("2:2").AutoFilter
        .Range("Item").AutoFilter Field:=1, Criteria1:="<>" & Tracking(i, 1)
        .UsedRange.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
        .Rows("2:2").AutoFilter
        .Range("Location").AutoFilter Field:=2, Criteria1:="<>" & Tracking(i, 2)
        .UsedRange.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With


Next i

End Sub

***Note that the named range ("Summary") from above is two columns in width.***
***Note that the named range ("Item") from above is the 1st of the two columns from the named range ("Summary").***
***Note that the named range ("Location") from above is the 2nd of the two columns from the named range ("Summary").***

In my new workbook, I no longer have those named ranges as the table object headers match the named ranges.

So I believe that I need to adjust the code that references ranges and named ranges, to correctly reference the Tables and the corresponding column/column header.

Do I or can I simply continue to use references to ranges in my code instead of the table objects?

I am kind of new to re-structuring code from range to listed objects, so I was hoping I could get some pointers, assistance, ideas, etc.

What do you think?

-Spydey

 

[MARK858]

Re: Convert Range vba code to ListedObject vba code --- New at this ....

I am not sure why that is happening - - I get the same error

Possibly because a table is an object within the worksheet maybe (a bit like saying Shape.entirerow.delete)?

 

[spydey]

Re: Convert Range vba code to ListedObject vba code --- New at this ....

Possibly because a table is an object within the worksheet maybe (a bit like saying Shape.entirerow.delete)?

That is actually quite insightful!!! Makes sense.

Thank you @MARK858 !!

-Spydey

 

[Yongle]

Re: Convert Range vba code to ListedObject vba code --- New at this ....

I have done some further testing
- use a variable to capture the address as a string and use that to define the range to be deleted

Deleting directly FAILS

    Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Getting the address and putting that inside Range in ONE command FAILS

    Range(Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Address).EntireRow.Delete

But using a variable to capture the address SUCCEEDS

    Dim addr As String
    addr = Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Address
    Range(addr).EntireRow.Delete

 

[Yongle]

Walked away from screen ... and finally "the penny dropped"

Doh!

    On Error Resume Next
    Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).[COLOR=#ff0000]Rows.[/COLOR]EntireRow.Delete

 

[MARK858]

Not home to test but does the below work?

DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Delete

 

[Yongle]

@MARK858

That also works and throws a message asking if user wants to delete entire row

 

[MARK858]

Would need a Display.Alerts False then, thanks for testing.

 

[spydey]

@Yongle & @MARK858. Thanks for testing out a few snippets of code.

I appreciate the assistance and clarification.

I will have to try the .rows.entirerow.delete.

-Spydey