Select rows containing numbers between 1 and 4

[sjippy]

Hi i like to make a Macro that makes a selection of variable rows .

So I can copy this selection or print etc…

Column “E” is populated with numbers between 1 and 4 so is was fiddling around with =< but I’m far from being close of figuring out of what syntax to use, I have no VBA experience.

I thought to be smart and made a counter with in the sheet and thought to copy this variable value and copy it to a selection while using a macro but it just copy the value that is used while recording the macro so that’s not very useful.

I would be grateful with some insight on how to resolve this issue.

 

[Mortiray]

You can use the Union() function to join two ranges in excel VBA together. Try the following code:

Sub SelectRowsLessThanFour()

    'Sets the total range to search through to all cells in the E column that have data
    Dim ColE As Range: Set ColE = Range("$E$1:$E$" & ActiveSheet.Columns(5).Find("*", SearchDirection:=xlPrevious).Row)
    
    'Range object that will accumulate all cells to select
    Dim RowsToSelect As Range
    
    'Runs through each cell in ColE
    Dim c As Range
    For Each c In ColE
        'If the value being checked is between 1 and 4
        If c.Value <= 4 And c.Value >= 1 Then
            
            'Add the row of the cell being scanned to RowsToSelect.
            If RowsToSelect Is Nothing _
            Then Set RowsToSelect = ActiveSheet.Rows(c.Row) _
            Else: Set RowsToSelect = Union(RowsToSelect, ActiveSheet.Rows(c.Row))
            
        End If
    Next c
    
    'Select all the rows
    RowsToSelect.Select
    
End Sub

 

[sjippy]

You can use the Union() function to join two ranges in excel VBA together. Try the following code:

Sub SelectRowsLessThanFour()

    'Sets the total range to search through to all cells in the E column that have data
    Dim ColE As Range: Set ColE = Range("$E$1:$E$" & ActiveSheet.Columns(5).Find("*", SearchDirection:=xlPrevious).Row)
   
    'Range object that will accumulate all cells to select
    Dim RowsToSelect As Range
   
    'Runs through each cell in ColE
    Dim c As Range
    For Each c In ColE
        'If the value being checked is between 1 and 4
        If c.Value <= 4 And c.Value >= 1 Then
           
            'Add the row of the cell being scanned to RowsToSelect.
            If RowsToSelect Is Nothing _
            Then Set RowsToSelect = ActiveSheet.Rows(c.Row) _
            Else: Set RowsToSelect = Union(RowsToSelect, ActiveSheet.Rows(c.Row))
           
        End If
    Next c
   
    'Select all the rows
    RowsToSelect.Select
   
End Sub

Wow great answer and works like a charm thanks!