asked on

How can I delete rows based on the mon of the date in column A

How can I delete rows based on the mon of the date in column A

My code below breaks with an error on this line : theDate = Cells(1, i)
clearRowsByMonth.xlsm
Sub Delete_Rows_Based_On_Value()

Dim theDate As Date
Dim valueOfMonth As String

Dim i As Long
i = CountRows("sheet1", "A") 'get the lastRow

While i > 0
theDate = Cells(1, i)
'valueOfMonth = Format(theDate, "MMM/dd/yyyy")
valueOfMonth = Format(theDate, "MMM")
If (StrComp(valueOfMonth, "Jun") <> 0) Then
Rows(1, i).EntireRow.Delete
End If
i = i - 1
Wend
End Sub

Function CountRows(Sheetname As String, colName As String) As Long
Dim i As Long
i = 0
i = ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlDown)).Rows.Count

CountRows = i
End Function

Martin Liss

theDate = Cells(i, 1)

Your 1 and i were reversed.

Martin Liss

Sub Delete_Rows_Based_On_Value()

Dim theDate As Date
Dim i As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row  'get the lastRow

Application.ScreenUpdating = False

For i = LastRow To 1 Step -1
    On Error Resume Next
    theDate = Cells(i, 1)
    If Month(theDate) = 6 Then
        Cells(i, "A").EntireRow.Delete
    End If
    On Error GoTo 0
Next

Application.ScreenUpdating = True

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christopher Schene

ASKER

I modified your second solution slightly to handle variable range

Sub deleteRowsFaster()

Dim intCriteria As Integer
Dim strInput As String

strInput = InputBox("Please enter the month number to be deleted", "Delete Month", 1)
' Criteria 21 is January
If Format(strInput, "00") < "01" Or Format(strInput, "00") > "12" Then
MsgBox "Invalid month number"
Exit Sub
End If

intCriteria = CInt(strInput) + 20

Application.ScreenUpdating = False

With ActiveSheet

' Criteria 21 is January
Dim cellRange As String
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'get the lastRow
cellRange = "$A$1:$A$" + CStr(lastRow)

.range(cellRange).AutoFilter Field:=1, Criteria1:= _
intCriteria, Operator:=xlFilterDynamic

.UsedRange.Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
If .AutoFilterMode Then
.AutoFilterMode = False
End If
End With

Application.ScreenUpdating = True
End Sub

Martin Liss

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020