locked
How to select multiple rows RRS feed

  • Question

  • Hi Guys,

    I am new to VBA and struggling with an extremely simple task : I want to select multiple, distinct rows based on a cell's value. I wrote this, but it has a problem : only one line gets selected, the last one. How can I make all rows being selected ?

    Sub SelectRows()
    Dim selectedRange As Range
        For Each thisCell In ActiveSheet.Range("X6:X186").Cells
          If (thisCell.Value = 0) Then thisCell.EntireRow.Select
          Next thisCell
    End Sub
    
    Thanks in advance !
    Thursday, November 21, 2013 7:08 AM

Answers

  • It usually isn't necessary to select a range in code. You can specify the range, then manipulate it in code without selecting it.

    Sub SelectRows()
        Dim selectedRange As Range
        Dim thisCell As Range
        For Each thisCell In ActiveSheet.Range("X6:X186").Cells
            If thisCell.Value = 0 Then
                If selectedRange Is Nothing Then
                    Set selectedRange = thisCell.EntireRow
                Else
                    Set selectedRange = Union(selectedRange, thisCell.EntireRow)
                End If
            End If
          Next thisCell
          ' You can do something with selectedRange here, for example delete it:
          selectedRange.Delete
          ' Or color it:
          selectedRange.Interior.Color = vbYellow
          ' Or select it if you really want to:
          selectedRange.Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Lac Thursday, November 21, 2013 7:28 AM
    Thursday, November 21, 2013 7:22 AM

All replies

  • It usually isn't necessary to select a range in code. You can specify the range, then manipulate it in code without selecting it.

    Sub SelectRows()
        Dim selectedRange As Range
        Dim thisCell As Range
        For Each thisCell In ActiveSheet.Range("X6:X186").Cells
            If thisCell.Value = 0 Then
                If selectedRange Is Nothing Then
                    Set selectedRange = thisCell.EntireRow
                Else
                    Set selectedRange = Union(selectedRange, thisCell.EntireRow)
                End If
            End If
          Next thisCell
          ' You can do something with selectedRange here, for example delete it:
          selectedRange.Delete
          ' Or color it:
          selectedRange.Interior.Color = vbYellow
          ' Or select it if you really want to:
          selectedRange.Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Lac Thursday, November 21, 2013 7:28 AM
    Thursday, November 21, 2013 7:22 AM
  • Thanks a lot Hans for the quick reply.

    The reason is that I actually want to delete the selection manually after reviewing it and not from the code.

    Thanks again, I knew this should be simple :-)

    Thursday, November 21, 2013 7:28 AM
  • you can try something like below:

    Sub SelectRowsG()
        Dim c As Range
        Dim rngG As Range
        For Each c In Intersect(ActiveSheet.UsedRange, Columns("a"))
            If c = "G" Then
                If rngG Is Nothing Then Set rngG = c.EntireRow
                Set rngG = Union(rngG, c.EntireRow)
            End If
        Next c
        rngG.Select
    End Sub
    

    change column to your preferred column

    change "G" to your preferred value.

    Thursday, November 21, 2013 7:37 AM