VBA Select range using Find method and variables

[Giggs1991]

Hi All,

I have a set of values in Column A of a worksheet named "Data".
Cell A1 has the value as "Description"
Cell A9 is blank
Cell A10 has the value as "Team Total"
The current active cell is D100
I would like to use the VBA "Cells.Find(What:="Description" statement to get to A1 from D100. Store the address of cell A1 in a variable named rng1. Then use the VBA "Cells.Find(What:="Team Total" statement to get to A10 from A1. Store the address of A10 in a variable called rng2. Next I would like to select the range A1 to A10 using a statement like =Range(rng1:rng2).

 

[LazyBug]

I don't understand your goal, but if worksheet "Data" has no more cells with "Description" and "Team Total", it should work:

Sub giggs()
Dim rng1 As Range, rng2 As Range, sh As Worksheet
    Set sh = ActiveWorkbook.Sheets("Data")
    Set rng1 = sh.UsedRange.Find("Description", , xlValues)
    Set rng2 = sh.UsedRange.Find("Team Total", , xlValues)
    
    sh.Range(rng1, rng2).Select
End Sub

 

[Giggs1991]

I don't understand your goal, but if worksheet "Data" has no more cells with "Description" and "Team Total", it should work:

Sub giggs()
Dim rng1 As Range, rng2 As Range, sh As Worksheet
    Set sh = ActiveWorkbook.Sheets("Data")
    Set rng1 = sh.UsedRange.Find("Description", , xlValues)
    Set rng2 = sh.UsedRange.Find("Team Total", , xlValues)
  
    sh.Range(rng1, rng2).Select
End Sub

Thank you...it works

 

[LazyBug]

Glad to help & thanks for the feedback.

 

[Giggs1991]

Glad to help & thanks for the feedback.

although it works, i just found that the variable rng1 hold the value "Description". Is there a way to assign the address value of this cell to rng1?

 

[LazyBug]

Default property of rng1 (rng1 is a variable with type range) is value, if you need an address, use rng1.address