Structured reference in VBA to a specific row and range of columns

Question

I am trying to iterate through a ListObject and, depending on whether a row is visible or not, select certain columns of that row to copy and paste into another sheet. I currently have the following code:

Set tbl = ActiveSheet.ListObjects("MasterBudget")
For r = 1 To tbl.ListRows.Count
    If tbl.ListRows(r).Range.RowHeight <> 0 Then 'not hidden/filtered
        tbl.ListColumns("Item description").DataBodyRange(r).Select                   
        Range("[@[Item description]:[Unit 3]]").Select '<-- problem line                   
    End If
Next r

It does get to the right item, but I for some reason can't figure out how to select that particular set of columns (from item description through unit 3). What am I doing wrong?

Any suggestions highly appreciated.

score 2 · Accepted Answer · answered Sep 08 '18 at 08:26

2

The @ notation only makes sense when entered in a cell that is inside the ListObject in question.

From code, you already have rows and columns you want, so the cells you want is an intersection of the two:

Dim tbl As ListObject
Dim ColumnsOfInterest As Range
Dim r As Long

Set tbl = ActiveSheet.ListObjects("MasterBudget")
Set ColumnsOfInterest = tbl.DataBodyRange.Worksheet.Range(tbl.ListColumns("Item description").Range, tbl.ListColumns("Unit 3").Range)

For r = 1 To tbl.ListRows.Count
    If tbl.ListRows(r).Range.RowHeight <> 0 Then 'not hidden/filtered
        Dim RangeToCopy As Range
        Set RangeToCopy = Application.Intersect(tbl.ListRows(r).Range, ColumnsOfInterest)

        RangeToCopy.Copy ...
    End If
Next r

Note that you don't have to select anything.

answered Sep 08 '18 at 08:26

GSerg

76,472
17
159
346

Both responses solve the issue, but this one is a bit cleaner to read. – Charlene Barina Sep 08 '18 at 10:37
GSerg, realised my range is non-contiguous for ColumnsOfInterest (skips the second column) - any suggestion on how to combine both a first column, then cols 3-10, in that variable? – Charlene Barina Sep 08 '18 at 11:56
1

great! I used this thanks to that method 3: `Set ColumnsOfInterest = Application.Union(tbl.ListColumns("Item description").Range, tbl.DataBodyRange.Worksheet.Range(tbl.ListColumns("Unit cost (VND)").Range, tbl.ListColumns("Unit 3").Range))` – Charlene Barina Sep 08 '18 at 12:29

score 1 · Answer 2 · answered Sep 08 '18 at 08:15

This is probably not the most elegant but dealing with structured ListObject table references rarely are.

Dim tbl As Object, r As Long

Set tbl = ActiveSheet.ListObjects("MasterBudget")

For r = 1 To tbl.ListRows.Count
    If tbl.ListRows(r).Range.RowHeight <> 0 Then 'not hidden/filtered
        tbl.ListColumns("Item description").DataBodyRange(r).Select
        ActiveSheet.Range(tbl.ListColumns("Item description").DataBodyRange(r), _
                          tbl.ListColumns("Unit 3").DataBodyRange(r)).Select
    End If
Next r

You could also use tbl.parent in place of ActiveSheet.

Structured reference in VBA to a specific row and range of columns

2 Answers2