Select range in VBA, defined by cell value

[jebenexcel]

Hi.

Imagine you have a table that starts with the column A and ends with the column M.
The table has a variable amount of rows. Is it possible to select this variable range through VBA, using the COUNTA function?
Like, Range(A1: M(COUNTA(A:A)).select or something along these lines...

 

[louisH]

Hi.

If you want to select the whole table you might just try :

Range("TableName").Select

If you want to do it your way :

Range("A1:M" & WorksheetFunction.CountA(Range("A:A"))).Select

 

[Nine Zero]

When it comes to VBA i tend to use lastrow as my go to for this

Dim lastrow As Integer
Dim r As Range

With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set r = Range("A1:A" & lastrow)

 

[jebenexcel]

Is it possible to refer to a count from a different sheet?
Range("A1:M" & WorksheetFunction.CountA(Range("othersheet!A:A"))).Select ?

 

[louisH]

Yes, but with this syntax

Range("A1:M" & WorksheetFunction.CountA(Worksheets("Other Sheet").Range("A:A"))).Select

 

[jebenexcel]

Thanks guys!