Select upto last available Row

  • Thread starter Thread starter Atif
  • Start date Start date
A

Atif

Hi

I am using below function to select fix range, how can I change "100" with a
variable, so it select from "A2" upto last row which have entry.

Range("A2:AN100").Select

Regards
 
Assuming you mean the last row in Column A thru N with a formula or constant
value in it...

LastRow = Range("A:AN").Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
Range("A2:AN" & LastRow).Select

Change the xlFormulas to xlValues if you want have only constant values in
your cells or, if there are formulas in them, if you want to ignore formulas
that evaluate to the empty string ("").
 
Dim myRange as Excel.Range
Dim aWS as Excel.Worksheet
Dim lRow as long

Set aWS = ActiveSheet
Set myRange = aWS.Range("A2")
lRow = aWS.Cells(aWS.Rows.Count,myRange.column).end(xlup).row

Set myRange = myRange.resize(lrow - myrange.row + 1, 1)
myRange.Select

HTH,
Barb Reinhardt
 
Back
Top