Delete all rows with dates in "Sheet 1", column C that are prior to the date in last row of "Sheet 2", column C

[VBAProIWish]

Hello All,

I would like to create a macro that deletes all rows in "Sheet1", Column C that have a date prior to the date in the last cell (bottommost) in "Sheet2" Column C.

Note that I don't want a popup box asking for a date. Instead, I want the date to be based on the last cell (bottommost) in "Sheet2", Column C.


Here's the laymen's-term code version of what I need
1. Select "Sheet2"
2. Go to the last cell (row) with data in column C
3. "Remember" that date
4. Select "Sheet1"
5. Delete any rows in "Sheet 1", Column C that have a date prior to the "Remembered" date from "Sheet2", Column C

Thanks much!

 

[Trebor76]

Hi VBAProlWish,

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim dteDateCheck As Date
    Dim i As Long, j As Long, k As Long
    Dim rngDelete As Range
    
    Application.ScreenUpdating = False
    
    'Grab the date in the last row of Col. C in Sheet2
    k = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row
    dteDateCheck = Sheets("Sheet2").Range("C" & k)
    
    'Create a range of any row(s) in Sheet1 where the date is less than the date in the 'dtrDateCheck' variable
    j = 2 'Starting row number in Sheet1
    k = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
    For i = j To k
        If CDate(Sheets("Sheet1").Range("C" & i)) < CDate(dteDateCheck) Then
            If rngDelete Is Nothing Then
                Set rngDelete = Sheets("Sheet1").Range("C" & i)
            Else
                Set rngDelete = Union(rngDelete, Sheets("Sheet1").Range("C" & i))
            End If
        End If
    Next i
    
    'If the 'rngDelete' range has been set, then...
    If Not rngDelete Is Nothing Then
        '...delete the rows from it
        rngDelete.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[VBAProIWish]

Thanks much Robert!
I only had to add an "=" next to the "<" and it works perfectly!

 

[Trebor76]

Thanks much Robert!

You're welcome

I only had to add an "=" next to the "<" and it works perfectly!

This will delete dates equal to or less than the date in the last row of Col. C of Sheet2. You said "Delete any rows in "Sheet 1", Column C that have a date prior to the "Remembered" date from "Sheet2", Column C"
If though it's working as expected that's great

 

[VBAProIWish]

Ohhh, you are absolutely right! Excel is very specific which is what I love about it.
Your grade went from a 99.9 to a 100!

Thanks again!