Compare length of two rows

[Legacy 108787]

In a large sheet I have several pivottables, using a dynamic range of sourcedata. I want one empty column between two tables.

Due to the dynamic range and some conditions for (self genererated) tabletitles, the cell for the next table to start can be different every time around. I therefore have to check which of two rows is longest, to have the next table exactly two cells further, leaving one blank column.

I have the following code:

Sub Last_Cell()
    Dim One As Range         'title (string) row
    Dim Two As Range         'content of table row
    Dim Fin As Range
    Set One = Range("IV5").End(xlToLeft).Offset(0, 2)
    Set Two = Range("IV11").End(xlToLeft).Offset(0, 2)
    If One > Two Then
        Set Fin = One
    Else
        Set Fin = Two
    End If
    Fin.Select
End Sub

This obviously doesn't work as One and Two are non-comparable, Row-wise.
I tried adding .Row, or [R], Address... but I'm stuck. I can't find anthing on here nor on Google.

 

[Yard]

Have you tried

One.Rows.Count

 

[Legacy 108787]

Have you tried

One.Rows.Count

I have now :P
But they both return =1, if I hover over them when f8'ing my way through. The right cell isn't selected either afterwards.

 

[Legacy 108787]

Sub Last_Cell()
    Dim One As Range
    Dim Two As Range
    Dim Fin As Range
    Set One = Range("A5", Range("IV5").End(xlToLeft))
    Set Two = Range("A11", Range("IV11").End(xlToLeft))
    If One.Columns.Count > Two.Columns.Count Then
        Set Fin = Range("IV5").End(xlToLeft).Offset(0, 2)
    Else
        Set Fin = Range("IV11").End(xlToLeft).Offset(0, 2)
    End If
    Fin.Select
End Sub

This does the trick!
But is seems overcomplicated to me, comparing ranges instead of two single cells.
Alas, I have my code now. Thanks for the help, Yard!

 

[Yard]

Looking back, I think you actually want the number of the rightmost column, not to count the rows!

One.Column

 

[Legacy 108787]

Looking back, I think you actually want the number of the rightmost column, not to count the rows!

One.Column

That's it!
Thanks again!


Though: quicky question:
If I keep this as one Sub, how can I call this Sub from another so that the Dim values are valid in the last Sub?