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
" wrote:
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