Excel VBA - select a dynamic cell range

Question

I want to be able to dynamically select a range of cells  the heading row   where the row is 1 but the columns with be for 1 to last column  where  A  is the first Column and where  M  is the last column  I know how to find the last column  but I don t know how to modified the below range to input the first and last column as  A  and  M     Range  A1 M1   Select

User · Answer

So it depends on how you want to pick the incrementer, but this should work:

Range("A1:" & Cells(1, i).Address).Select

Where i is the variable that represents the column you want to select (1=A, 2=B, etc.). Do you want to do this by column letter instead? We can adjust if so :)

If you want the beginning to be dynamic as well, you can try this:

Sub SelectCols()

    Dim Col1 As Integer
    Dim Col2 As Integer

    Col1 = 2
    Col2 = 4

    Range(Cells(1, Col1), Cells(1, Col2)).Select

End Sub

User · Answer

If you want to select a variable range containing all headers cells   Dim sht as WorkSheet Set sht   This Workbook Sheets  Data     Range Cells 1 1  Cells 1 Columns Count  End xlToLeft   Select   lt  lt  lt  NOT ROBUST  sht Range sht Cells 1 1  sht Cells 1 Columns Count  End xlToLeft   Select      as long as there s no other content on that row   EDIT  updated to stress that when using Range Cells       Cells       it s good practice to qualify both Range and Cells with a worksheet reference

User · Answer

I like to used this method the most  it will auto select the first column to the last column being used  However  if the last cell in the first row or the last cell in the first column are empty  this code will not calculate properly  Check the link for other methods to dynamically select cell range   Sub DynamicRange    Best used when first column has value on last row and first row has a value in the last column  Dim sht As Worksheet Dim LastRow As Long Dim LastColumn As Long Dim StartCell As Range  Set sht   Worksheets  Sheet1   Set StartCell   Range  A1     Find Last Row and Column   LastRow   sht Cells sht Rows Count  StartCell Column  End xlUp  Row   LastColumn   sht Cells StartCell Row  sht Columns Count  End xlToLeft  Column   Select Range   sht Range StartCell  sht Cells LastRow  LastColumn   Select  End Sub

User · Answer

sub selectVar        dim x y as integer     let srange    A   amp  x  amp       amp   m   amp  y     range srange  select end sub   I think this is the simplest way

[excel] Excel VBA - select a dynamic cell range

Examples related to excel

Examples related to range

Examples related to vba