CODEWITHSUNDEEP
excelvba
Brandon
Brandon

Reputation: 5

Fill in blank cells of a column within a table

Excel Sheet Example

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

Upvotes: 0

Views: 757

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

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

Upvotes: 1

Related Questions