VBA Selecting range by variables

Question

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

User · Answer

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   amp  Startrow  amp       amp   D   amp  Lastrow Range Copyrange  Select End Sub

User · Answer

I tried using   Range cells 1  1   cells lastRow  lastColumn   Select    where lastRow and lastColumn are integers  but received run-time error 1004   I m using an older VB  6 5    What did work was to use the following      Range Chr 64   firstColumn   amp  firstRow  amp       amp  Chr 64   lastColumn   amp  firstColumn  Select

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[vba] VBA: Selecting range by variables

Examples related to vba

Examples related to variables

Examples related to range

Examples related to selection