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.

GSerg · Accepted Answer · 2018-09-08 08:26:05Z

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 8, 2018 at 8:26

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charlene Barina Over a year ago

Both responses solve the issue, but this one is a bit cleaner to read.

Charlene Barina Over a year ago

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 Over a year ago

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

user4039065 · Accepted Answer · 2018-09-08 08:15:04Z

1

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.

answered Sep 8, 2018 at 8:15

user4039065

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related