user1741874
user1741874

Reputation:

Setting up a dynamic range in excel-vba

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

Answers (2)

whytheq
whytheq

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:

enter image description here

Upvotes: 6

tigeravatar
tigeravatar

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

Related Questions