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
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
Last Comment
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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
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
VBA
Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.
17K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Your 1 and i were reversed.