Reputation:
I'm basing my code off of this. Excel VBA - select a dynamic cell range
I'm trying to find the syntax to create a dynamic range. Example: I always start on D8 but the upper bound of the range is based on an int count in another cell. [h4]
Dim count As Integer
count = Sheet2.Cells(8,1).Value
Set refRng = Sheet2.Range("D8:" & Cells(8, i).Address)
Is the relevant code sample.
I now know that Sheet2.Range("H1") doesn't return an int, it returns a variant or something?
I've tried a million different things and have figured out that none of them work. There has to be a better way to set up a dynamic range.
Upvotes: 2
Views: 59487
Reputation: 35605
Not 100% sure what you're trying to achieve but in terms of messing around with ranges maybe this is a start:
Option Explicit
Sub select_Range()
Dim count As Integer
count = ThisWorkbook.Worksheets("Sheet2").Range("A8").Value
Dim i As Integer
i = count
Dim refRng As Excel.Range
Set refRng = ThisWorkbook.Worksheets("Sheet2").Range("D8:D" & i)
refRng.Select
End Sub
This results in the following on Sheet2
:
Upvotes: 6
Reputation: 26660
This was originally a comment, but it is also the solution so I am adding it as an answer
Cells(8, 1)
= "A8". If you want cell H1 it would be Cells(1, 8)
or Cells(1, "H")
. If you want cell H4 it would be Cells(4, 8)
or Cells(4, "H")
.
Alternately, just Range("H1")
or Range("H4")
Upvotes: 2