VBA to select rows and columns for dynamic range

[RChapman9]

Hello!

I need to set a dynamic range for formatting a report. The number of rows and columns will change regularly, and not all of the rows will have data. I put this together based on research in this forum, but I'm getting a "Compile error: invalid or unqualified reference" on ".Cells". Can anyone help with correcting this? Thanks!!

    Dim lrow As Long
    Dim lcol As Long
    Dim onerng As Range
    
    With Sheets("Solutions by Location")
    lrow = .Range("a" & .Rows.Count).End(xlUp).Row
    lcol = .Cells(lrow, .Columns.Count).End(xlToLeft).Column
    End With
    
    Range("A2").Select
    Set onerng = .Cells(lrow, 1).Resize(, lcol)
    Range("onerng").Select

'continue with code to format the selection

 

[Fluff]

Assuming that it was this line that gave the error

 Set onerng = .Cells(lrow, 1).Resize(, lcol)

Remove the . from infront of .Cells

 

[steve the fish]

You are going to get error if 'Solutions by Location' is not the activesheet also.

 

[RChapman9]

Fluff, thanks so much! That got me beyond that line, but now I'm getting Run-time error: 1004 Method Range of object Global failed on the next line, Range("onerng").Select. Any further thoughts? Thanks again!

 

[Fluff]

As you've declared onerng as Range it should be

onerng.Select

 

[RChapman9]

Fluff, thank you again! At least it's not erroring out anymore.
Steve, yes, that is the active sheet.

It worked this time, but it only selected the last row, not from the starting point ("A2") to the last row, last column. Is there a way to make something like this work? Thank you!

    Range("a2:" lastcol & lrow).Select

 

[Fluff]

Try

Range("a2",Cells(lrow,lastcol).Select

 

[RChapman9]

It worked!!! Fluff, you have "taught me to fish" today. I struggle with dynamic ranges frequently, and now I feel like I have a solution I can go to regularly. Thank you oh so much!!

 

[Fluff]

Glad to help & thanks for the feedback