MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Rows(a:b).Select - Variables


Posted by Phil Ridley on February 07, 2001 7:20 PM

I am attempting to feed variables into a Rows in VB in Excell. I have the first variable "X" and the second "Y". I have tried Rows(X:Y).Select, Rows ("X":"Y").Select but to no avail.

Any help is appreciated.


Posted by Dave Hawley on February 07, 2001 8:29 PM

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

Posted by Phil Ridley on February 07, 2001 9:15 PM

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

Posted by Dave Hawley on February 08, 2001 12:22 AM

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