Fill in blank cells of a column within a table

Question

This is more of an efficiency question. I am trying to fill blank cells with the date within a specific column of a master excel sheet that's updated monthly. Data is entered outside of column A first from a child workbook so I can make use of a last row range line. Here is my code:

    fillDate = Format(Date, "Mmm-YY")
    If fillDate <> vbNullString Then
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            If Range("A" & i).Value = vbNullString Then
                Range("A" & i).Value = fillDate
            End If
        Next i
   End If

And this works just fine but I'm wondering how to make this more efficient? Instead of starting at i = 1 every time I'd like to be able to make that dynamic. My issue is I can't find the last row of JUST column A since it's in a table and a last row will always bring back row 1150 instead of row 1138 of column A (keeping in mind that the last row of column A will change every month as data is entered into the excel sheet)[refer to image].

Thank you for your time

See [this question](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table) for how to find the last row in a table. — BigBen, Apr 28 '20 at 01:23
If your long column is always column 7... then in your code... you just temporarily insert an empty column before 7.... then you can find the last row of table at column 1... after you're done, you just delete column 7.... — Huy Pham, Apr 28 '20 at 01:38

score 1 · Accepted Answer · answered Apr 28 '20 at 01:34

Alternatively:

Sub tester()
    Dim rng As Range
    With ActiveSheet.ListObjects("Table1").ListColumns("MyDateCol").DataBodyRange
        On Error Resume Next 'ignore error if no blanks
        Set rng = .SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rng Is Nothing Then rng.Value = Format(Date, "Mmm-YY")
    End With
End Sub

Fill in blank cells of a column within a table

1 Answers1