selecting rows using a variable.

[Guest]

I have this code...
Dim x1 As Integer
Dim x2 As Integer
Dim x3 As Integer
Columns("B:B").Select

Selection.Find(What:="miscellaneous", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
x1 = ActiveCell.Row
Columns("A:A").Select

Selection.Find(What:="ms totals", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
x2 = ActiveCell.Row
x3 = (x1:x2)

Rows(x3).Select

With this I am attempting to extract a few rows. I search for
"Miscellaneous" in column B, save the row number as x1. Search for "ms
totals" in column A, saving the row number as x2. All I want to do is Select
the Rows between those two numbers, seems easy probably is. I will be very
grateful to anyone who can help me at all, thanks a lot.
Nick Cherry

 

[Bob Phillips]

Using your code

Dim x1 As Integer
Dim x2 As Integer
Dim x3 As Integer

Columns("B:B").Find(What:="miscellaneous", _
LookIn:=xlFormulas, _
LookAt:=xlPart).Activate
x1 = ActiveCell.Row

Columns("A:A").Find(What:="ms totals", _
LookIn:=xlFormulas, _
LookAt:=xlPart).Activate
x2 = ActiveCell.Row

Rows(x1 & ":" & x2).Select


but I would do it like this

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Columns("B:B").Find(What:="miscellaneous", _
LookIn:=xlFormulas, _
LookAt:=xlPart)

If Not rng1 Is Nothing Then

Set rng2 = Columns("A:A").Find(What:="ms totals", _
LookIn:=xlFormulas, _
LookAt:=xlPart)

End If

If Not rng2 Is Nothing Then

rng1.Resize(rng2.Row - rng1.Row + 1).EntireRow.Select

End If

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Rick Hansen]

Nick, Good Morning From the Land of the Midnight Sun,
Here A little bit of code I believe will get to job done for ya.

enjoy, Rick (Fairbanks, AK)

Sub testme()
Dim rngBB As Range, rngFound As Range
Dim rngAA As Range
Dim x1 As Integer, x2 As Integer

Set rngBB = Range("B:B")
Set rngAA = Range("A:A")

Set rngFound = rngBB.Find(What:="miscellaneous", LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
_
xlNext, MatchCase:=False)

If Not rngFound Is Nothing Then
x1 = rngFound.Row
Else
MsgBox ("miscellaneous not found")
Exit sub
End If

Set rngFound = Nothing
Set rngFound = rngAA.Find(What:="ms totals", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then
x2 = rngFound.Row
Else
MsgBox ("ms total not found")
Exit Sub
End If

Range(x1 & ":" & x2).EntireRow.Select
End Sub