Excel 2007 VBA Find row based on date

Question

Date | data | data | data
12/29|   G  |   F  |  G
12/30|   G  |      |

I have a spreadsheet like above. I want to find the row that is the current date, then reference the row that is the current date in a Range type. Then cycle through the data in that row.

I can find the current date, and get the address of the cell that is the current date:

dateRange = "A1:" & regionSheet.Range("A1").End(xlDown).Address

    For Each cell In regionSheet.Range(dateRange)
      If cell.Value = Date Then
      row = cell.Address
      End If
    Next cell

That returns $A$2. I need to somehow turn this into a Range type. I tried using the cell.Address like below:

row = cell.Address & ":" & regionSheet.Range(row).End(xlRight).Address

but that errors out.

Maybe I'm going about this the wrong way? Any ideas? Thanks!

Answer 1

range(cell, cell.End(xlToRight)).Address 

OR

range(cell.Address, range(cell.Address).End(xlToRight)).Address 

EDIT: If you want it to have it in Range type, you could use range(cell, cell.End(xlToRight))

Answer 2

Be warned that the End() function can return incorrect results if there are gaps in the data. For example, if you had data in the second and fourth columns, End will not give you the result you want.

You could try something like this (assumes your data starts in row 1 and column 1):

Sub RowOfCurrentDate()

    Dim lngCurrDateRow As Long
    Dim lngNumCols As Long

    Dim rngDates As Range
    Dim rngToday As Range
    Dim c As Range

    'Get current region and count the number of columns
    Set rngDates = Range("A1").CurrentRegion
    lngNumCols = rngDates.Columns.Count

    'Resize the range down to one column
    Set rngDates = rngDates.Resize(rngDates.Rows.Count, 1)

    'Find today's date in the range
    lngCurrDateRow = Application.WorksheetFunction.Match(CLng(Date), rngDates, 0)

    'Set the range to search through for today
    Set rngToday = Range(Cells(lngCurrDateRow, 1), Cells(lngCurrDateRow, lngNumCols))

    'then loop through all cells in that range
    For Each c In rngToday
        'if cell is not empty
        If Len(c) > 0 Then
            'do something
        End If
    Next c

End Sub