CODEWITHSUNDEEP
vbaexceldate
Caleb Sewell
Caleb Sewell

Reputation: 21

Selecting Multiple Rows in Excel

I use a massive tracker that stores the dates on which sites have been built. I am trying to look through a range of cells and determine if the date is today's date. I wrote a tiny Excel sheet to make it less complicated before implementing in my tracker.

This is what I am working with.

Excel Screenshot

The problem is that it only selects one line with today's date when there are more.

This is the selection that Excel makes when the button is pressed.

Excel Selection

How do I select more than one row? (They might not be next to each other. It might be row 1, 5, and 8 in the future).

'   Date Selection Macro
'   Goal! - Bring automated cell selection based on date range
'   TODO - Create macro to select rows that have a given date range
'   TODO - Create a window that allows you to add the given date range 
'   rather than hard code
'   TODO - Export selected rows to an email template in Outlook

Sub DateSelection()
    Dim completionDate As Range
    Set completionDate = Range("B2:B8")

    Dim todaysDate As Date
    todaysDate = Date

    For Each cell In completionDate
        If cell = todaysDate Then
            cell.EntireRow.Select
        Else
            'cell.Font.ColorIndex = 3
        End If
    Next
End Sub

Upvotes: 2

Views: 2156

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

You need Union like this:

Sub DateSelection()

  Dim completionDate As Range
  Set completionDate = Range("B2:B8")
  Dim rng As Range
  Dim todaysDate As Date
  todaysDate = Date

  For Each cell In completionDate

      If cell = todaysDate Then
          If rng Is Nothing Then
            Set rng = cell.EntireRow
          Else
            Set rng = Union(rng, cell.EntireRow)
          End If
      Else
          'cell.Font.ColorIndex = 3

  End If

  Next
  rng.Select
End Sub

However, you also could use filter to only show rows with todays date and then select all that is shown. Would do the same :)

Upvotes: 3

Related Questions