Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with VBA Text
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with VBA Text
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." wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with VBA Text
On Nov 16, 11:06 am, Dave Peterson wrote:
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." wrote: 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- Hide quoted text - - Show quoted text - thx so much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text does not display in "Text boxs" and when wrapping text in a c | Excel Discussion (Misc queries) | |||
Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
convert a range of lowercase text to upper text or vice versa | Excel Worksheet Functions | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |