×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Selecting cells in Excel using variables

Selecting cells in Excel using variables

Selecting cells in Excel using variables

(OP)
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.

RE: Selecting cells in Excel using variables

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

RE: Selecting cells in Excel using variables

Use

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

RE: Selecting cells in Excel using variables

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

RE: Selecting cells in Excel using variables


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

RE: Selecting cells in Excel using variables

Just another way...

CODE

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

RE: Selecting cells in Excel using variables

(OP)
Thank you for all your replies. Using

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

worked wonders.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources