Not able to get dynamic range.

[Jagdev Singh]

Hi Experts

I want to capture the used range from a sheet. The code which I am using right now is

Dim LastRow1 As Long
   
    LastRow1 = ActiveSheet.UsedRange.Rows.Count
Range("$B$5:$P$5" & LastRow1).Select

My data range starts from B5

I am getting the exact count when I run the code and put my cursor on LastRow1 (55 rows), but the range gets selected till 554.

Am I doing something wrong here.

Regards,
JD

 

[Hui]

Try:

LastRow1 = Range("B" & Rows.Count).End(xlUp).Row
Range("$B$5:$P$" & LastRow1).Select

Choose a Column ie: "B" that is guaranteed to have the last piece of data in it

 

[Marc L]

Hi !

UsedRange is not the filled data range but also the used cells
(format, borders, …) even with empty data !

MsgBox ActiveSheet.UsedRange.Address …

See also CurrentRegion.
​

 

[Jagdev Singh]

Hi Hui

Thanks it is working Fine!

@Marc L Thanks for looking into it.

Regards,
JD