VBA excel macro that iterates a table and select all rows which row cell at column A equals something

Question

I'm trying to iterate a table and get index from all rows which Cell (cell for date) at position A (column A) equals to some date which date i get from another cell lets say H1 cell. I have a button with attached macro on it when clicked. The table has more than 10000 rows and have 7 columns ( A, B, C, D, E, F, G) the A column represents date. In javascript it would be somthing like this.

All the cells inside column A have day/month/year values. And the whole table is for an year only. So the table starts from 01/01/2017 and will end at 31/12/2017.

const myDate = worksheet.getCell('H1').Value;
const columnA = worksheet.getColumns('A') // here i use pseudocode 
columnA.forEach((cell, index) => {
    if (cell.Value == myDate)
      console.log(index); // instead i can push each index in array so later i could forEach the rows with these indexes and then do some manipulation.
});

My task basically is to put a date string into an H1 cell. And when button is clicked All the rows which first cell ( column A ) equals H1 cell must be printed.

Edited:

Tried this so far and i'm storing and indexes where the day starts and where it ends. So I have the range. Now how can I select all the cells which rows are between firstRow and lastRow and Print them out.

Sub FindMyNubmer()
    Dim a As Range, b As Range
    Dim firstRow As Long
    Dim lastRow As Long
    Set a = Range("A1:A65000")

    For Each b In a.Rows
        If b.Value = Range("H4").Value Then
            If firstRow = "0" Then
                firstRow = b.Row
            End If
            lastRow = b.Row
        End If
    Next
    MsgBox firstRow & " - " & lastRow
End Sub

paul bica paul bica · Accepted Answer · 2017-09-10T20:09:32

Try AutoFilter; I used cells H4 and H5 on Sheet1 to show an example of a date range criteria

Option Explicit

Public Sub MarkDates()
    Dim ws As Worksheet, colA As Range, lc As Long, hdr As Long

    Set ws = Sheet1
    Set colA = ws.UsedRange.Columns(1)
    lc = ws.UsedRange.Columns.Count + 1     'Today's date in last col

    Application.ScreenUpdating = False

    With colA

        .AutoFilter Field:=1, _
                    Criteria1:=">=" & CDbl(ws.Range("H4")), _
                    Operator:=xlAnd, _
                    Criteria2:="<=" & CDbl(ws.Range("H5"))

        If .SpecialCells(xlCellTypeVisible).CountLarge > 1 Then
            hdr = Abs(Not IsDate(.Cells(1)))
            With ws.UsedRange.Columns(lc)
                .Offset(hdr).Resize(.Rows.Count - hdr, 1) = Date    'Last used column
                .NumberFormat = colA.NumberFormat
            End With
        End If

        .AutoFilter

    End With

    Application.ScreenUpdating = True

End Sub

VBA excel macro that iterates a table and select all rows which row cell at column A equals something

1 Answers