VBA: Selecting range by variables

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!

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.