ListRows with Range and Cells strange behaviour (unexpected offset by 1, Range.Range)

user_19485242 0
Viewed 35

Hello all,
Glad to post my first question.
I can't quite get my head around a specific behavior of Range and Cells. It's in context of ListRows, but to be honest I'm not sure if it's relevant.
I have a named table "temp_table" and what I need in the end is for each row to assign the sum of columns 2-5 to column 6.
I can't even get to Sum part, because I have problems with referencing the Range to be summed.
In sample below I use breakpoint, so it shows behaviour for first row only.

Dim temp_table As Object
Dim temprow As Object

Set temp_table = Worksheets("temp").ListObjects("temp_table")
With temp_table 'I use this With for the rest of my code preceeding the problematic part.
    
    For Each temprow In .ListRows
            
        With temprow
            MsgBox .Range.Address 'shows $A$2:$F$2
            MsgBox .Range.Range(.Range.Cells(2), .Range.Cells(5)).Address 'shows $B$3:$E$3
            MsgBox .Range.Range(.Range.Cells(1,2), .Range.Cells(1,5)).Address 'shows $B$3:$E$3
            
            'the workaround I found:
            MsgBox .Range.Range(.Range.Cells(0,2), .Range.Cells(0,5)).Address 'shows $B$2:$E$2
       End With
            
    Next temprow
End With

To be honest, I don't get a few aspects of it:

  1. Searching documentation I never found (0,n) indexing for .Range.Cells. It works as if I used Offset property, but I don't. So I'm baffled why is it like this?
    EDIT: Not even Offset, because columns would be wrong... Why only rows are this strange?
  2. Why do I need a double .Range.Range?
    temprow.Range.Range(.Range.Cells(2), .Range.Cells(5)).Address works, but temprow.Range(.Range.Cells(2), .Range.Cells(5)).Address crashes with Type Mismatch error?
  3. Even accepting the fact, that I need doubled Range expression, if I change the test code to:
With temprow.Range 'instead of With temprow
MsgBox .Address 'works
MsgBox .Range(.Range.Cells(0, 2), .Range.Cells(0, 5)).Address 'throws error

I end up with Run-Time error 450: Wrong number of arguments or invalid property assignment.

I'd really like to understand what's going on. For some reason referencing Ranges is something very basic I always end up being the most unsure of, and this one really left me puzzled.

EDIT:
I tried just going with my workaround for now and started with snippet below:

For Each temprow In .ListRows

    With temprow
        Set sum_range = .Range.Range(.Range.Cells(0, 2), .Range.Cells(0, 5))
        Debug.Print sum_range.Address
    End With
            
Next temprow

I have 397 rows of the table, and last Prints from Debug.Print are:

$B$780:$E$780
$B$782:$E$782
$B$784:$E$784
$B$786:$E$786
$B$788:$E$788
$B$790:$E$790
$B$792:$E$792
$B$794:$E$794

So it seems, that my workaround is not even a workaround.
I'll try Ike's suggestion, but please is anyone able to tell me what's going on?

listobject cell range vba excel
1 Answers

You don't need the "double range" stuff - use the range- object of the listrow.

To get retrieve columns 2 to 5 use resize.

  • lr.range returns the whole (table/list) row
  • lr.range(1,2) returns the cell of second column
  • lr.range(1,2).resize(1,4) increases the range to include 4 cells to the right.
Sub useListobject()

Dim lo As ListObject
Set lo = ActiveSheet.ListObjects("temp_table")

Dim lr As ListRow

For Each lr In lo.ListRows
    'first rows output:   1  $A$2:$F$2     $B$2:$E$2
    Debug.Print lr.Index, lr.Range.Address, lr.Range(1, 2).Resize(1, 4).Address
Next

End Sub
user_165784240
Related