VBA: Selecting range by variables

I recorded a macro with 'Relative References' and this is what I got :

Range("F10").SelectActiveCell.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 StringMyRange = "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 StringStartrow = 1Lastrow = 11Let Copyrange = "A" & Startrow & ":" & "D" & LastrowRange(Copyrange).SelectEnd Sub

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.

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)).SelectEnd 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 RangeWith Sheet1    Set rLastCell = .Cells.Find("*", .Cells(1, 1), xlValues, xlPart, , xlPrevious)    .Range(.Cells(1, 1), rLastCell).SelectEnd 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).SelectSelection.Font.Bold = True

You can

.Range(.Cells(1,1), rLastCells).Font.Bold = True