VBA: Selecting range by variables
Solution 1
I recorded a macro with 'Relative References' and this is what I got :
Range("F10").Select
ActiveCell.Offset(0, 3).Range("A1:D11").Select
Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:
Dim MyRange as String
MyRange = "A1:D11"
Range(MyRange).Select
And it worked :) ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it ;)
Following tested and found working :
Sub Macro04()
Dim Copyrange As String
Startrow = 1
Lastrow = 11
Let Copyrange = "A" & Startrow & ":" & "D" & Lastrow
Range(Copyrange).Select
End Sub
Solution 2
I ran into something similar - I wanted to create a range based on some variables. Using the Worksheet.Cells did not work directly since I think the cell's values were passed to Range.
This did work though:
Range(Cells(1, 1).Address(), Cells(lastRow, lastColumn).Address()).Select
That took care of converting the cell's numerical location to what Range expects, which is the A1 format.
Solution 3
If you just want to select the used range, use
ActiveSheet.UsedRange.Select
If you want to select from A1 to the end of the used range, you can use the SpecialCells method like this
With ActiveSheet
.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With
Sometimes Excel gets confused on what is the last cell. It's never a smaller range than the actual used range, but it can be bigger if some cells were deleted. To avoid that, you can use Find and the asterisk wildcard to find the real last cell.
Dim rLastCell As Range
With Sheet1
Set rLastCell = .Cells.Find("*", .Cells(1, 1), xlValues, xlPart, , xlPrevious)
.Range(.Cells(1, 1), rLastCell).Select
End With
Finally, make sure you're only selecting if you really need to. Most of what you need to do in Excel VBA you can do directly to the Range rather than selecting it first. Instead of
.Range(.Cells(1, 1), rLastCell).Select
Selection.Font.Bold = True
You can
.Range(.Cells(1,1), rLastCells).Font.Bold = True
Solution 4
You're missing a close parenthesis, I.E. you aren't closing Range()
.
Try this Range(cells(1, 1), cells(lastRow, lastColumn)).Select
But you should really look at the other answer from Dick Kusleika for possible alternatives that may serve you better. Specifically, ActiveSheet.UsedRange.Select
which has the same end result as your code.
Solution 5
you are turning them into an address but Cells(#,#) uses integer inputs not address inputs so just use lastRow = ActiveSheet.UsedRange.Rows.count
and lastColumn = ActiveSheet.UsedRange.Columns.Count
Fabian Stolz
Updated on April 23, 2020Comments
-
Fabian Stolz about 4 years
I want to select the formatted range of an Excel sheet. To define the last and first row I use the following functions:
lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
In the next step I want to select this area: Formula should look like this:
Range(cells(1, 1), cells(lastRow, lastColumn).Select
However, this is not working. Maybe somebody has an idea what is wrong with it. Thanks a lot!
-
ssarabando almost 12 yearsWhat exactly isn't working? I tried filling up the range B2 to D4 with random data and your code selected A1 to D4. Isn't that what you'd expect?
-
-
Chris Bates almost 10 yearsActually firstColumn should read lastRow ==> Range(Chr(64 + firstColumn) & firstRow & ":" & Chr(64 + lastColumn) & lastRow).Selec
-
Michelle over 8 yearsThanks, I have spent all morning trying to figure out how to fix a syntax issue because of this. :)
-
Balaji R almost 7 yearsThank you you saved my day!