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 ....

Perhaps the links below will help you with how to refer to ranges in tables...

https://jkp-ads.com/articles/Excel2007TablesVBA.asp
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

 

[spydey]

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

@ Mark858 Thanks for the links.

I have read and re-read the 2nd one several times. I have gotten a bunch of info from it, but feel that I am still lacking to complete the circle.

I will have to take a look at the first link as that one is new to me.

Thanks again!!

-Spydey

 

[Yongle]

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

This may help
- there are several "defined ranges" that can be used directly with tables

    Dim ws As Worksheet: Set ws = Sheets("Sheet X")
    Dim Tbl As ListObject
    Dim WholeTable As Range, DataOnly As Range, HeaderRow As Range
    Dim Column3 As Range, Column4 As Range, BothColumns As Range
    Dim Column3Data As Range, Column4Data As Range, BothColumnsData As Range
    Dim VisibleData As Range
[COLOR=#006400]
'if only one table then[/COLOR]
    Set Tbl = ws.ListObjects(1)
'or refer by name
    Set Tbl = ws.ListObjects("Tbl_Invoicing")

[COLOR=#006400]'can refer to specific bits of table[/COLOR]
    Set WholeTable = Tbl.Range
    Set DataOnly = Tbl.DataBodyRange
    Set HeaderRow = Tbl.HeaderRowRange
    Set Column3 = Tbl.ListColumns(3).Range
    Set Column4 = Tbl.ListColumns(4).Range
    Set BothColumns = Union(Column3, Column4)
    Set Column3Data = Tbl.ListColumns(3).DataBodyRange
    Set Column4Data = Tbl.ListColumns(4).DataBodyRange
    Set BothColumnsData = Union(Column3Data, Column4Data)

[COLOR=#006400]'using above variable[/COLOR]
    Set VisibleData = DataOnly.SpecialCells(xlCellTypeVisible)
[COLOR=#006400]'which is same as this[/COLOR]
    Set VisibleData = Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    
    Debug.Print 1, Tbl.Name
    Debug.Print 2, WholeTable.Address
    Debug.Print 3, HeaderRow.Address
    Debug.Print 4, Column3.Address
    Debug.Print 5, Column4.Address
    Debug.Print 6, Column3Data.Address
    Debug.Print 7, Column4Data.Address
    Debug.Print 8, BothColumns.Address
    Debug.Print 9, BothColumnsData.Address
    Debug.Print 10, VisibleData.Address

[COLOR=#006400]'how many rows[/COLOR]
    Debug.Print "rows of data", Tbl.DataBodyRange.Rows.Count

[COLOR=#006400]'etc....[/COLOR]
End Sub

 

[Yongle]

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

The code for sorting and filtering is exactly the same as with normal ranges but make use of the "defined ranges" to make life simpler

Sub SampleTableCode()

    With Sheets("Sheet X").ListObjects(1)

[COLOR=#006400]'filtering table on 4th column[/COLOR]
    .Range.AutoFilter Field:=4, Criteria1:=">500", Operator:=xlAnd

[COLOR=#006400]'sort table using 4th column[/COLOR]
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=.[COLOR=#ff0000]HeaderRowRange(4)[/COLOR], SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Instead of Key:=.HeaderRowRange(4)

one way to filter based on "name of header" is to use VBA to find that header (here it is "Sales") and filter on that
Key:=.HeaderRowRange.Find("Sales")

Or use a structured reference like this
Key:=Range("Tbl_Invoicing[[#All],[ Sales]]")

Personally, I find Range.Find method much easier to adapt to different tables

 

[spydey]

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

@Yongle

That is perfect!! Thanks for the info and making it very clear.

I think (hopefully .... crossing fingers ....) that I can use what MARK858 and you have shared, together to accomplish what I am trying to do.

I appreciate it!

Take care.

-Spydey

 

[spydey]

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

Ok, so I have gone through the two links that @MARK858 provided (thanks again Mark!!).

I have also used the code references that @Yongle provided. Those were very helpful!! (Thanks again Yongle).

However, I have run into an issue. I think that maybe I am not using the .EntireRow.Delete correct.

Here is my adjusted code:

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


With wb.Worksheets("Sheet1").ListObjects("Summary")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Summary[[#Headers],[Generate]]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With




Set rng = wb.Worksheets("Sheet1").Range("Summary[[Item]:[Location]]")


    Tracking = rng.Value


    For i = LBound(Tracking) To UBound(Tracking)
    
    wb.Worksheets.Copy
            
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
                
    With ws.ListObjects("Summary")
        .Range.AutoFilter Field:=2, Criteria1:="<>" & Tracking(i, 1)
        .DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
		.Range.AutoFilter Field:=2
        .Range.AutoFilter Field:=3, Criteria1:="<>" & Tracking(i, 2)
        .DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range.AutoFilter Field:=3
    End With
	
	End Sub

Every time I get to the first .DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete , I get a debug error.

I can't seem to figure it out.

Any thoughts as to why it isn't working, what I coded incorrectly, how to fix it?

I am going to keep researching too.

Thanks for your help!!

-Spydey

 

[spydey]

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

I think I figured it out.

It really should be:

[COLOR=#333333].DataBodyRange.SpecialCells(xlCellTypeVisible).Delete[/COLOR]

Let me know if I am wrong, but it seems to work ....

-Spydey

 

[Yongle]

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
- I will try and understand why when I find some time next week


Test this workaround which seems to work for me

   Dim cel As Range
            For Each cel In Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
                On Error Resume Next
                cel.EntireRow.Delete
                On Error GoTo 0
            Next

 

[spydey]

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
- I will try and understand why when I find some time next week


Test this workaround which seems to work for me

   Dim cel As Range
            For Each cel In Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
                On Error Resume Next
                cel.EntireRow.Delete
                On Error GoTo 0
            Next

Thanks @Yongle !!.

I found how to get it to work.

I just use:

[COLOR=#333333].DataBodyRange.SpecialCells(xlCellTypeVisible).Delete[/COLOR]

rather than

[COLOR=#333333].DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete[/COLOR]

Appears that it works as it should.

However, I am curious as to why the .EntireRow.Delete wasn't working.

If you find a reason, please feel free to share with me.

Thanks for all your help so far!

-Spydey