Help with Defining Dynamic Range with VBA

[Joyner]

Hello, I can't find the right the fit for defining my range.

I want to set a range from cell A5 (always) down to a cell in Column A that contains the word "Total" then up one row and over to column H.

So if the word "Total" is in row 10, my range should be A5:H9 and the row for the word "total" can change base on the output of the sheet, so I need to find the word "total" in column A to define the range.

If the solution would be difference, I also would like to define a similar range but only in column A, so the range would be from A5 down to the cell containing "Total" and up one. For this range I intend to use it in:

Application.Max("myRange").Value if that is correct

Thanks for any help or suggestions.

 

[VoG]

Try like this

Sub Test()
Dim FOUND As Range, r As Range
Set FOUND = Columns("A").Find(what:="Total", lookat:=xlWhole, LookIn:=xlValues)
Set r = Range("A5:A" & FOUND.Row - 1).Resize(, 8)
'test it
r.Select
End Sub

 

[shg]

You can do this with just defined names:

myRng1 refers to: =Sheet1!$A$5:$A$5:INDEX(Sheet1!$H:$H, MATCH("Total", Sheet1!$A:$A, 0) - 1)

myRng2 refers to: =Sheet1!$A$5:$A$5:INDEX(Sheet1!$A:$A, MATCH("Total", Sheet1!$A:$A, 0) - 1)

 

[Joyner]

Thanks VoG

Thank Works perfect.

shg thanks for the reply - I needed a VBA solution because I can't just define a named range since this is run on an output that I don't control, unless I missed something as far as applying the solution.

Thanks again.

 

[shg]

No problem, glad VoG's solution worked. I thought you needed a dynamic range.