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
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
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
sub selectVar ()
dim x,y as integer
let srange = "A" & x & ":" & "m" & y
range(srange).select
end sub
I think this is the simplest way.
Source: Stackoverflow.com