2

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.

Improve this question
3
  • 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") Commented Aug 28, 2013 at 15:12
  • Alternately, just Range("H1") or Range("H4") Commented Aug 28, 2013 at 15:12
  • Wowww. If you want to throw that down as an answer I'll upvote it. My stuff now works. Commented Aug 28, 2013 at 15:22

2 Answers 2

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.