Setting up a dynamic range in excel-vba

Question

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.

`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")` — tigeravatar, Aug 28 '13 at 15:12
Wowww. If you want to throw that down as an answer I'll upvote it. My stuff now works. — , Aug 28 '13 at 15:22

score 6 · Answer 1 · answered Aug 28 '13 at 15:28

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:

enter image description here

score 2 · Accepted Answer · answered Aug 28 '13 at 15:24

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")

Setting up a dynamic range in excel-vba

2 Answers2