Dynamic Range based on cell contents

[Guest]

Excel 2003

I am trying to use a macro to search column a for the Text "RD". I have
used the code below many times to find each instance in a column. Now, I
want to find each instance, but after one instance, assign the value to a
variable, go to the next instance, assign that to a variable, and then
perform some operations. on that range. I'm only concerned at this point
with getting the selection and variable assignments to work. The code below
works for the first instance, but always assigns 5 to J. I am stumped.
Thanks in advance for any help.

Sub SelectRDRows()
Dim I As Long
Dim J As Long
Dim BegCell As Long
Dim EndCell As Long
For I = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & I).Value = "RD" Then
EndCell = I - 1
For J = Range("A" & EndCell).End(xlUp).Row To 1 Step -1
If Range("A" & J).Value = "RD" Then
BegCell = J + 1
Range("A" & BegCell & ":" & "A" & EndCell).Select
Exit For
End If

Next J
End If
Next I
End Sub

 

[Guest]

I'm not sure what you ae trying to do, but you need to keep on advancing the
end point. Your code is always going back to 65536. I think the change
below is what you may need.

Sub SelectRDRows()
Dim I As Long
Dim J As Long
Dim BegCell As Long
Dim EndCell As Long
EndCell = 65536
For I = Range("A" & EndCell).End(xlUp).Row To 1 Step -1
If Range("A" & I).Value = "RD" Then
EndCell = I - 1
For J = Range("A" & EndCell).End(xlUp).Row To 1 Step -1
If Range("A" & J).Value = "RD" Then
BegCell = J + 1
Range("A" & BegCell & ":" & "A" & EndCell).Select
Exit For
End If

Next J
EndCell = BegCell - 1
End If
Next I
End Sub

 

[Guest]

Joel, Thank you for the quick response. Your help is much appreciated. I'm
sorry if I wasn't clear in my previous post. Here is a small description of
the problem I am encountering.

The sample sheet that I have contains 695 rows of data in column A. THis
number could vary which is why I am trying to set up a dynamic range. The
first loop starts at Row 695 as I would expect. The first time the text "RD"
appears in column A is at Row 694. Then, The EndCell variable is assigned a
variable of 693 as I would also expect. The problem I am having is that when
the second loop starts, it always begins with Row 5 and counts down to 0.
The next time "RD" appears is at Row 641, but I can't get the loop to start
searching at "A693" as I would like. Your code to assign EndCell a value of
65536 does exactly what my code did before. BegCell is never receiving a
value, so all the line of code you added at the end does is assign -1 to
EndCell. Again, forgive me if I am not being clear in my problem. I would
expect the code that I have to start the 2nd loop at Row 693 and search until
the next "RD" appearance at Row 641. At this point, I will Select the range,
do a few things, and then exit back to the first loop to get the next 2
occurences of "RD" in column A.

 

[Guest]

Sub SelectRDRows()
Dim I As Long
Dim J As Long
Dim BegCell As Long
Dim EndCell As Long
EndCell = Range("A" & Rows.Count).End(xlUp).Row
Do While EndCell > 0
If Range("A" & EndCell).Value = "RD" Then
EndCell = EndCell - 1
BegCell = EndCell
For J = BegCell To 1 Step -1
If Range("A" & J).Value = "RD" Then
BegCell = J + 1
Range("A" & BegCell & ":" & "A" & EndCell).Select
BegCell = BegCell - 1
Exit For
End If
Next J
EndCell = BegCell
End If
EndCell = EndCell - 1
Loop
End Sub

 

[Guest]

Thank you Joel. This worked great! I appreciate your help and patience.