VBA to Select Dynamic Range

[nc_waggoner]

Good morning,

I have a workbook with 3 sheets of similar format. Each returns data starting in cell "P3:W" but can return a different number of rows on each sheet. For example, SheetA returned 5 rows so I would need to select "P3:W7" but on SheetB there is only one row so I only need to select "P3:W3". I was using the below code which worked for SheetA but selected far too many rows on SheetB:

Dim r As Long


r = Selection.End(xlDown).Row


Range("P" & Selection.Row, "W" & r).Select

2 thoughts...is there a code to select any range? Or is there a code to reference a "counter" cell (ex. Cell Z1) and select that many rows.

 

[James Lever]

hi,

Range("p3").CurrentRegion.Select, can be used but be aware if there are row or column gaps. If there are then you will need to be a little more creative.

 

[nc_waggoner]

There are no gaps in columns, only X amount of rows and formulas beyond that to return blank ("") values if not applicable.

 

[Fluff]

How about

Range("P3", Range("P" & Rows.Count).End(xlUp).Offset(, 7)).Select

 

[nc_waggoner]

Fluff, this did not work. It highlighted many rows due to there being formulas in them. Is there a way to count if one of the columns (say R,S,T) is greater than 0?

 

[Fluff]

In that case how about

   Dim Lr As Long
   Lr = Range("P:P").Find("*", , xlValues, , xlbyrws, xlPrevious, , , False).Row
   Range("P3:W" & Lr).Select