Selecting cells in Excel using variables 1

[Grafton54]

I need to select a range of cells from (1,1) to (i,8), where i is variable computed during the execution of my VBA routine.

Microsoft documentation (and my book) shows a way you can select the cells if the co-ordinates are hard-coded, e.g. range("a2:b3").select

However, how do you do that if you have a variable? I will need to convert i to an alphabet. Fine if i is less than 26, what if it is more?

I also tried range((1,1),(i,8)).select but that returned a syntax error.

Does anybody know the right way to get round it? Many thanks.

 

[CajunCenturion]

Here is an example that might help.

Dim StartCellLtr As String
Dim StartCellNum As Integer
Dim EndCellLtr As String
Dim EndCellNum As Integer
Dim TheRange As String

StartCellLtr = "A"
StartCellNum = 1
EndCellLtr = "D"
EndCellNum = 4

TheRange = StartCellLtr & Trim(StartCellNum) & ":" & EndCellLtr & Trim(EndCellNum)
Range(TheRange).Select

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ab123456]

Use

Range(Cells(1, 1), Cells(i, 8)).Select

 

[cummings54]

Use the Cells method to select a range when the extents of the range are variable. Here's an example

Sub SelectionExample()
Dim iRow As Long, iCol As Long
iRow = 5
iCol = 6
With Sheet1
Set a = .Range(.Cells(iRow, iCol), .Cells(iRow + 4, iCol + 2))
End With
a.Select
End Sub

The example selects a range 5 rows by 3 colums on Sheet2

 

[IFRs]

You can use various constructs, such as

For I = 1 to 3
ExcelFileObj.Worksheets(WkSht).Range("A" & J).Value = J * 2
Next

or, since I like to use named ranges:

TestValue = ExcelFileObj.Worksheets(1).Range("RangeName").Value

 

[yakpol]

Just another way...

Range(1,1).resize(i,8)

 

[Grafton54]

Thank you for all your replies. Using

Range(Cells(1, 1), Cells(i, 8)).Select

worked wonders.