VBA Code for selection rows

[Guest]

Ok I know how to write the code for selecting a row. Here is an example:
Rows("8:27").Select

What if instead of the number i want to use vairables such as:
Rows("x:y").Select

Can anyone tell me how to do this? If you can please email
(e-mail address removed)

 

[STEVE BELL]

Try this:

Rows(x & ":" & y).Select

only text goes in " " and variables stay out.

 

[Guest]

What sort of thing do you want to do with this facility? It will help to get
you a better solution.

 

[Guest]

Copy and paste this procedure into a module and then run it on a worksheet.
If this doesn't give you hours of enjoyment, I don't know what will.



Sub Rows_SelectVariable()

Dim ibxTop As String
Dim ibxBottom As String
Dim sngTop As Single
Dim sngBottom As Single


ibxTop = InputBox("Enter the row number that's the TOP row of your rows
selection", _
"What is your UPPERMOST Row ?", _
ActiveCell.Offset(1, 0).Row)

If Len(ibxTop) > 0 Then
sngBottom = CSng(ibxTop) + 1
Else
sngBottom = ActiveCell.Offset(2, 0).Row
End If

ibxBottom = InputBox("Enter the row number that's the BOTTOM row of your
rows selection", _
"What is your LOWERMOST Row ?", _
sngBottom)


If Len(ibxTop) = 0 Or Len(ibxBottom) = 0 Then

MsgBox "You clicked the ""Cancel"" button for one or both of the
requested row numbers. " & _
"This procedure will be aborted." _
, vbOKOnly + vbCritical, _
"Apprarently, you don't want or need no stinking rows ... "

ActiveCell.Select

Exit Sub

ElseIf ibxTop = 0 Or ibxBottom = 0 Then

MsgBox "You entered an invalid row address of (zero)." & _
"This procedure will be aborted." _
, vbOKOnly + vbCritical, _
"Apprarently, you don't want or need no stinking rows ... "

ActiveCell.Select

Exit Sub

End If

sngTop = CSng(ibxTop)
sngBottom = CSng(ibxBottom)

If sngBottom < sngTop Then

MsgBox "You entered an invalid pair of row addresses:" & vbCr & vbCr
& _
"You entered " & Format(sngTop, "0") & " for your TOP row." &
vbCr & _
"You entered " & Format(sngBottom, "0") & " for your BOTTOM
row." & vbCr & vbCr & _
"The value for BOTTOM row CAN'T BE LESS THAN THE TOP row. " &
vbCr & _
"The BOTTOM row MUST BE GREATER THAN OR --for " & _
"a one-row selection-- EQUAL TO the top row." & vbCr & vbCr & _
"This procedure will be aborted." _
, vbOKOnly + vbCritical, _
"Apprarently, you don't want or need no stinking rows ... "

sngTop = 0
sngBottom = 0

Exit Sub

Else

MsgBox "The " & Format(sngBottom - sngTop + 1, "0") & _
"-contiguous-row selection you've indicated is: " & vbCr & vbCr
& _
"(" & sngTop & ":" & sngBottom & ")" _
, vbOKOnly + vbInformation, _
"I got your rows ... "


ActiveCell.Offset(sngTop - ActiveCell.Row, 0).Range(Cells(1, 1),
Cells(sngBottom - sngTop + 1, 1)).EntireRow.Select

End If

sngTop = 0
sngBottom = 0

End Sub