Rows(a:b).Select - Variables

Hi Phil

I'm guessing your vaiables are either Integers or Longs (should be long if dealing with Rows) which would expain things. I note also you haven't used placed the : in quotations either. these 2 methods below will work

Sub TryThis()
Dim X As String, Y As String

X = 7
Y = 11
Rows(X & ":" & Y).Select
End Sub

Sub OrThis()
Dim X As Long, Y As Long

X = 7
Y = 11
X = Str(X)
Y = Str(Y)
Rows(X & ":" & Y).Select

End Sub

OzGrid Business Applications

I was wrong. The problem exists else where.
What I am trying to do is search down a sorted Column of data until I find a certain value and record what row it is then sort until I find a second value, record it then pass both to the Rows(x:y).select.

This is the code I have so far. I apologise for it, as it is only my second VB macro and would likely be full of redundancies.

Dim X As String, Y As String

Range("a1").Select

'Find end of CBH1
Do While Selection = "CBH1"
If Selection = "CBH1" Then Selection.Offset(1, 0).Select
Loop

'At begining of CBH2. Select all rows.
Do While Selection = "cbh2"
Range(EntireRow).Select = X
If Selection = "CBH2" Then Selection.Offset(1, 0).Select Else
Range(EntireRow).Select = Y
Loop

Rows(X & ":" & Y).Select

Hi again Phil

Try this method it will be much quicker:

Sub ShouldBeQuicker()
Dim X As String, Y As String
'Find the last occurence of CBH1 in Column A
X = Columns(1).Find _
(What:="CBH1", After:=Range("A65536"), _
SearchDirection:=xlPrevious).Row
'Find the last occurence of CBH2 in Column A
Y = Columns(1).Find _
(What:="CBH2", After:=Range("A65536"), _
SearchDirection:=xlPrevious).Row

Rows(X & ":" & Y).Select
End Sub

Also you had a syntax error in the line that passes the row number to your variables rather than:
Range(EntireRow).Select = Y

You should use:
Y = Selection.EntireRow.Row

Hope this helps

Dave

BTW if you ever need any training check out my web page under the link: "Training"

OzGrid Business Applications