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 · Accepted 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 '<<< 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

So it depends on how you want to pick the incrementer  but this should work   Range  A1    amp  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

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