Help with VBA Text

[Brandon G.]

Instead of always selecting every 20th row...I want VBA to prompt me
the quantity of rows. I think I can tell VBA to prompt the qty of
rows I want to select instead of defaulting to 20. Here is the VBA
string:

Sub SelectEveryNthRow()
' Initialize ColsSelection equal to the number of columns in the
' selection.
ColsSelection = Selection.Columns.Count
' Initialize RowsSelection equal to the number of rows in your
' selection.
RowsSelection = Selection.Rows.Count
' Initialize RowsBetween equal to Twenty.
RowsBetween = 20
' Initialize Diff equal to one row less than the first row number
of
' the selection.
Diff = Selection.Row - 1
' Resize the selection to be 1 column wide and the same number of
' rows long as the initial selection.
Selection.Resize(RowsSelection, 1).Select
' Resize the selection to be every twentieth row and the same
number of
' columns wide as the original selection.
Set FinalRange = Selection. _
Offset(RowsBetween - 1, 0).Resize(1, ColsSelection)
' Loop through each cell in the selection.
For Each xCell In Selection
' If the row number is a multiple of 3, then . . .
If xCell.Row Mod RowsBetween = Diff Then
' ...reset FinalRange to include the union of the current
' FinalRange and the same number of columns.
Set FinalRange = Application.Union _
(FinalRange, xCell.Resize(1, ColsSelection))
' End check.
End If
' Iterate loop.
Next xCell
' Select the requested cells in the range.
FinalRange.Select
End Sub

What can I change to make this adjustment?

Thanks

Brandon G.

 

[Dave Peterson]

You can use inputbox to get a response from the user. But this doesn't have to
be numeric.

But you can use application.inputbox to ask the user for a number.

Option Explicit
Sub SelectEveryNthRow2()

Dim myRng As Range
Dim myNthRng As Range
Dim HowMany As Long
Dim iCtr As Long

Set myRng = Selection.Areas(1)

HowMany = CLng(Application.InputBox(Prompt:="how many rows?", Type:=1))

If HowMany < 1 Then
Exit Sub
End If

If HowMany > myRng.Rows.Count Then
MsgBox "Hmmmm. Can't do that with this selection!"
Exit Sub
End If

'skip the first row
For iCtr = myRng.Row - 1 + HowMany To myRng.Rows.Count Step HowMany
If myNthRng Is Nothing Then
Set myNthRng = myRng.Rows(iCtr)
Else
Set myNthRng = Union(myNthRng, myRng.Rows(iCtr))
End If
Next iCtr

If myNthRng Is Nothing Then
MsgBox "Something bad happened"
Else
myNthRng.Select
End If

End Sub

 

[Brandon G.]

You can use inputbox to get a response from the user. But this doesn't have to
be numeric.

But you can use application.inputbox to ask the user for a number.

Option Explicit
Sub SelectEveryNthRow2()

Dim myRng As Range
Dim myNthRng As Range
Dim HowMany As Long
Dim iCtr As Long

Set myRng = Selection.Areas(1)

HowMany = CLng(Application.InputBox(Prompt:="how many rows?", Type:=1))

If HowMany < 1 Then
Exit Sub
End If

If HowMany > myRng.Rows.Count Then
MsgBox "Hmmmm. Can't do that with this selection!"
Exit Sub
End If

'skip the first row
For iCtr = myRng.Row - 1 + HowMany To myRng.Rows.Count Step HowMany
If myNthRng Is Nothing Then
Set myNthRng = myRng.Rows(iCtr)
Else
Set myNthRng = Union(myNthRng, myRng.Rows(iCtr))
End If
Next iCtr

If myNthRng Is Nothing Then
MsgBox "Something bad happened"
Else
myNthRng.Select
End If

End Sub









--

Dave Peterson- Hide quoted text -

- Show quoted text -

thx so much