VBA to select rows and columns for dynamic range

RChapman9

New Member
Joined
Apr 1, 2008
Messages
32
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!!

Code:
    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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Assuming that it was this line that gave the error
Code:
 Set onerng = .Cells(lrow, 1).Resize(, lcol)
Remove the . from infront of .Cells
 
Upvote 0
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!
 
Upvote 0
As you've declared onerng as Range it should be
Code:
onerng.Select
 
Upvote 0
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!

Code:
    Range("a2:" lastcol & lrow).Select
 
Last edited:
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top