Referencing specific Range of table's .DataBodyRange

Question

Let's say I have the following exemplary table

I have a for loop that runs through the entire ListColumn(2) (column C / 3) and ranks the "users" based on who has the most apples (eg. Michael would be 1, Robert 2, etc.)

However let's say, I want to reference only specific Range of the table
(eg. let's say the Range("C7:C9") <=> ListRows(3,4,5))
How can I do that?

I have the following code:

    Private Sub CommandButton1_Click()

    Dim tbl As ListObject: Set tbl = Sheets("Sheet1").ListObjects("Table1")
    Dim my_range As Range

    For Each my_range In tbl.ListColumns(2).DataBodyRange 
    ' ^ this runs through entire column instead of the specified range I want!
        my_range.Offset(0, 1) = WorksheetFunction.Rank(my_range, tbl.ListColumns(2).DataBodyRange)
         ' ^ again, isntead of entire DataBodyRange we should be rather referencing the specific Range
    Next my_range
End Sub

Basically, I need to somehow limit the .DataBodyRange itself into a specific range, problem is, .DataBodyRange is specified to either take entire Column/Row or only 1 Cell from the entire ListObject as .DataBodyRange([row index], [column index]).

So, in the presumed example of selecting Robert,Michael,Laurel ListRows(3, 4, 5) the Expected Result would be:

Any suggestions how to do this?

score 1 · Accepted Answer · answered Jul 21 '21 at 02:26

I know I'm late to the game, but for posterity...

Use .Offset and .Resize methods to do just about anything in the tbl.Range or tbl.DataBodyRange ...

To iterate only select rows:

...
Set my_range1 = tbl.Listcolumns(2).DataBodyRange
For LoopControl = StartRow To EndRow
    my_range1.Resize(1).Offset(LoopControl - 1).Value2 = 'your code here
    'Alternate code
    tbl.ListRows(LoopControl).Resize(, 1).Offset(, 2).Value2 = 'your code here
    'Or even:
    tbl.DataBodyRange.Resize(1, 1).Offset(2, LoopControl).Value2 = 'your code here
Next LoopControl

Note that you can use .ListColumns("Apples") in case the columns are rearranged after the fact. Also, you can .Offset(, tbl.ListColumns("Apples").Index) to adapt to changing table structure.

Or to refer to a subset of rows as a single range:

Set my_range = tbl.ListColumns(2).DataBodyRange.Offset(StartRow - 1).Resize(EndRow - StartRow + 1)
'Alternately:
Set my_Range = tbl.ListColumns(2).Range.Offset(StartRow).Resize(EndRow - StartRow + 1)

score 0 · Answer 2 · answered Aug 07 '18 at 21:51

0

You can reference consecutive cells in a column using something like:

With tbl.ListColumns(2).DataBodyRange
    For Each my_range in Range(.Cells(3), .Cells(5))
      ' Code here.
    Next my_range
End With

Is that what you mean?

answered Aug 07 '18 at 21:51

Lewis Kirby

67
2
10

Referencing specific Range of table's .DataBodyRange

2 Answers2