Delete Rows where current month is found in column A

Lawrence Salvucci
Lawrence Salvucci used Ask the Experts™
on
I need to be able to delete all rows where the date in column A is within the range in cell R2 & S2.

R2 = Start Date
S2 = End Date

I need to be able to do this via VBA code.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Change > to >= and < to <= if you want to include the edges of the date range

Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count

For lngRow = lngLastRow To 2 Step -1
    If Range("A" & lngRow).Value > Range("R2").Value And Range("A" & lngRow).Value < Range("S2").Value Then
        Range("A" & lngRow).EntireRow.Delete
    End If
Next

Open in new window

Commented:
You can do this with this code:

Sub DeleteRows()

    Dim lstrow As Long
    Dim x As Long
   
    CheckDate = DateSerial(Year(Now), Month(Now), 1)
    lstrow = Cells(Rows.Count, 1).End(xlUp).Row

    For x = lstrow To 3 Step -1
        If Cells(x, 1).Value < Range("R2") Or Cells(x, 1) > Range("S2") Then
            Rows(x).Delete
        End If
    Next

End Sub

Open in new window

Lawrence SalvucciDirector of Information Technology

Author

Commented:
How do I specify a specific sheet in my workbook in case I want to run this from another file or if I'm on a different sheet within that workbook?

lngLastRow = ActiveSheet.UsedRange.Rows.Count
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
lngLastRow = Sheets("Sheet1").UsedRange.Rows.Count
Lawrence SalvucciDirector of Information Technology

Author

Commented:
Thanks for the help! Much appreciated!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012

Commented:
@lsalvucci: did you at least tryed my code?!?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This line

If Cells(x, 1).Value < Range("R2") Or Cells(x, 1) > Range("S2") Then

would have deleted only dates outside of the range.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial