[VBA] How to Delete Rows with Past Dates in Word Tables | Automatically Clean Up Expired Rows

When managing schedules or logs in Word tables, you often need to delete rows containing dates that have already passed.

This article introduces a VBA code snippet that automatically deletes rows with dates earlier than “today.”

Goal

• Target: A Word table where the first column contains the date.
• Action: Delete rows where the date is “before today” to keep only upcoming schedules.
• Format: Handles Japanese date formats (e.g., “2023年12月1日”).

VBA Code

Executing the following code checks the table from the bottom row up to the second row. If it finds a date earlier than today, it deletes the entire row.

Sub UpdateTable()
    Dim doc As Document
    Set doc = ActiveDocument

    Dim tbl As Table
    Set tbl = doc.Tables(1) ' Target the first table

    Dim i As Integer
    ' Loop from bottom to top (starting from row 2)
    For i = tbl.Rows.Count To 2 Step -1 
        Dim dateString As String
        dateString = tbl.Cell(i, 1).Range.Text
        
        ' Remove the end-of-cell marker (last 2 characters)
        dateString = Trim(Left(dateString, Len(dateString) - 2)) 

        ' Convert "yyyy年mm月dd日" to "yyyy/mm/dd" for easier parsing
        dateString = Replace(dateString, "年", "/")
        dateString = Replace(dateString, "月", "/")
        dateString = Replace(dateString, "日", "")

        ' Check if the string is a valid date
        If IsDate(dateString) Then
            ' If the date is strictly before today
            If CDate(dateString) < Date Then
                tbl.Rows(i).Delete
            End If
        End If
    Next i
End Sub

Key Points of the Code

1. Loop from Bottom to Top (Step -1)

For i = tbl.Rows.Count To 2 Step -1

When deleting rows, you must loop from the bottom up. If you delete from the top, the row numbers shift, causing the code to skip rows or produce errors.

2. Cleaning the Cell Text

Trim(Left(dateString, Len(dateString) - 2))

Word table cells contain special end-of-cell characters (a bell character and a paragraph mark). You must remove these last two characters to get the clean text for the date.

3. Date Comparison

If CDate(dateString) < Date Then

CDate converts the string to a Date type, and Date returns the current system date. This logic deletes any row where the date is strictly in the past.

Important Notes

• Target Table: This code targets doc.Tables(1) (the first table in the document). Change the index number if you need to target a different table.
• Non-Date Cells: If the first column contains text that is not a date, IsDate() returns False, and the row is skipped.
• Including Today: The current code deletes dates before today. If you want to delete rows including today, change the condition to <= Date.

Summary

Manually deleting expired rows in a Word schedule can be tedious. Using VBA allows you to organize your table and keep only the latest information with a single click.

• Use Step -1 to delete rows safely.
• Use CDate and IsDate for accurate date handling.
• Always remove the special control characters from Word table cells before processing text.

Please try this method to streamline your business reports and management tables.