How do I Select multiple rows using multipe variables?

[thediscopossum]

I am currently attempting to select multiple rows (Rows are variables) to group via a macro.

The problem is, it is not selecting the rows i want.
It seems to be putting the variables together as opposed to adding the totals together
ie, if MSTextBox = 3 and AVTextBox = 4, It groups Rows 4-34. Not Rows 4-8

Also the variables are taken from a text box within a userform.

Private Sub MSTextBox_Change()
Dim MSTextBox As Integer
End Sub

Private Sub AVTextBox_Change()
Dim MSTextBox As Integer
End Sub

Private Sub FinishButton_Click()
Rows((MSTextBox + 1) & " : " & ((MSTextbox) + (AVTextBox))).Select
Selection.Rows.Group
End Sub

Any assistance is greatly appreciated

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The TextBox.Value property will be returned as a String data type.

It looks like you were trying to change the data type of the TextBoxes through statements like this, but that won't work.

Dim MSTextBox As Integer

Even though you are using an "+" addition operator, VBA concatenates the two strings instead of adding them.

Sometimes VBA will coerce Strings that have numeric values into Numbers, but a best practice in coding is to be explicit in using Conversion Functions to change the data type to what is needed.

Here's one way to code that.

Private Sub FinishButton_Click()

 If IsNumeric(MSTextBox.Value) And _
   IsNumeric(AVTextBox.Value) Then
   
   Rows(MSTextBox.Value & ":" & CStr( _
      CInt(MSTextBox.Value) + _
      CInt(AVTextBox.Value))).Group
 Else
   MsgBox "Invalid data: AV and MS must be numbers"
 End If
End Sub

It validates that the entries are numeric. You could go one step further and validate that the numbers are within a specified min and max value range.

 

[thediscopossum]

Thanks Jerry!
Worked perfectly.
Greatly appreciated