VBA to Delete Entire Row if Year is Older than Specified

[jski21]

Good day Mr. Excel Team,

Seeking some brief instruction as to how to to delete an entire row of data if the year in the cell of that row precedes the year entered into a message box. Sample data is:

PR07 - Voucher Drawdowns EN Only.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Number	Line Item	Project ID	Act ID	NumberDate	Status	StatusDate	SentDate	Code	PrjctYear	Type	Recipient TIN	Payee TIN	Program	Year	Amount	Qtr/Yr
2	170659	1	2	2	1/29/1998	Completed	01/29/1998	01/28/1998	B97MC390004	1997	EN	1	1	CDBG	1998	(77,591)	1Q98
3	174209	1	2	1794	2/13/1998	Completed	02/13/1998	02/14/1999	B97MC390004	1997	EN	2	2	CDBG	1998	36,349	1Q98
4	174209	2	2	1850	2/13/1998	Completed	02/13/1998	02/14/2000	B97MC390004	1997	EN	3	3	CDBG	1998	317	1Q98
5	174209	3	2	2008	2/13/1998	Completed	02/13/1998	02/14/2001	B97MC390004	1997	EN	4	4	CDBG	1998	648	1Q98
6	174209	4	19	2200	2/13/1998	Completed	02/13/1998	02/14/2002	B97MC390004	1997	EN	5	5	CDBG	1998	950	1Q98
7	174209	5	19	2202	2/13/1998	Completed	02/13/1998	02/14/2003	B97MC390004	1997	EN	6	6	CDBG	1998	1,266	1Q98
8	174209	6	19	2201	2/13/1998	Completed	02/13/1998	02/14/2004	B97MC390004	1997	EN	7	7	CDBG	1998	554	1Q98
9	177609	1	2	2	3/2/1998	Completed	03/02/1998	02/27/2005	B97MC390004	1997	EN	8	8	CDBG	1998	(50)	1Q98
10	179916	1	2	1837	3/11/1998	Completed	03/13/1998	03/14/2006	B97MC390004	1997	EN	9	9	CDBG	1998	111,920	1Q98
11	179916	2	4	2231	3/11/1998	Completed	03/13/1998	03/14/2007	B97MC390004	1997	EN	10	10	CDBG	1998	185,620	1Q98
12	179916	3	2	2210	3/11/1998	Completed	03/13/1998	03/14/2008	B97MC390004	1997	EN	11	11	CDBG	1998	572,750	1Q98
13	179916	4	128	2234	3/11/1998	Completed	03/13/1998	03/14/2009	B97MC390004	1997	EN	12	12	CDBG	1998	425,904	1Q98
14	179916	5	129	2236	3/11/1998	Completed	03/13/1998	03/14/2010	B97MC390004	1997	EN	13	13	CDBG	1998	455,496	1Q98
15	179923	1	29	2213	3/11/1998	Completed	03/13/1998	03/14/2011	B97MC390004	1997	EN	14	14	CDBG	1998	2,140	1Q98
16	179923	2	29	2212	3/11/1998	Completed	03/13/1998	03/14/2012	B97MC390004	1997	EN	15	15	CDBG	1998	960	1Q98
17	179923	3	29	2211	3/11/1998	Completed	03/13/1998	03/14/2013	B97MC390004	1997	EN	16	16	CDBG	1998	72	1Q98
18	179923	4	29	2219	3/11/1998	Completed	03/13/1998	03/14/2014	B97MC390004	1997	EN	17	17	CDBG	1998	686	1Q98
19	179923	5	29	2220	3/11/1998	Completed	03/13/1998	03/14/2015	B97MC390004	1997	EN	18	18	CDBG	1998	2,528	1Q98
20	179923	6	29	2221	3/11/1998	Completed	03/13/1998	03/14/2016	B97MC390004	1997	EN	19	19	CDBG	1998	72	1Q98
21	179923	7	29	2222	3/11/1998	Completed	03/13/1998	03/14/2017	B97MC390004	1997	EN	20	20	CDBG	1998	5,561	1Q98
22	179923	8	29	2209	3/11/1998	Completed	03/13/1998	03/14/2018	B97MC390004	1997	EN	21	21	CDBG	1998	750	1Q98
23	179923	9	29	2223	3/11/1998	Completed	03/13/1998	03/14/2019	B97MC390004	1997	EN	22	22	CDBG	1998	5,012	1Q98
24	179923	10	29	2225	3/11/1998	Completed	03/13/1998	03/14/2020	B97MC390004	1997	EN	23	23	CDBG	1998	36	1Q98
25	179923	11	29	2208	3/11/1998	Completed	03/13/1998	03/14/2021	B97MC390004	1997	EN	24	24	CDBG	1998	2,583	1Q98
26	179923	12	29	2224	3/11/1998	Completed	3/13/1998	03/14/2022	B97MC390004	1997	EN	25	25	CDBG	1998	3,101	1Q98
PR07 - Voucher Drawdowns EN Onl

The date field used would be Column H. Example: If one enters "2022" all the rows with years from 2021 and prior would be deleted.

Thanks in advance for your review, consideration, and assistance.


jski

 

[StephenCrump]

Seeking some brief instruction .....

One option:

- Filter the data for year <=2021 in column H
- Delete the unwanted rows (.SpecialCells(xlVisible).EntireRow.Delete, allowing for the possibility that there are no rows to be deleted)
- Unfilter.

The macro recorder will help you with the syntax.

Another option, if you can count on the dates in col H being in ascending order, would be to do a MATCH() using 31 Dec 2021, to find the last 2021 (or previous year) row number = N, say, and then delete rows 2 to N.

Please post back if you need help with the code.

 

[jski21]

Thanks Stephen for the guidance. I noodled on this a bit and thought it easier to dissect the data using column O. Came up with some initial code:

'Declared Variables
Dim StartYear As String

'Open Input Box and Ask User for a StartYear. Filter Column O on <StartYear
Columns("O:O").Select
StartYear = Application.InputBox("Please provide a start year", "StartYear", Type:=1)

'Filter Column O based on input Box criteria
ActiveSheet.ListObjects("PR07Table").Range.AutoFilter Field:=15, Criteria1 _
:="<StartYear", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.ClearContents
ActiveSheet.ListObjects("PR07Table").Range.AutoFilter Field:=15
Range("A2").Select

Having some trouble moving the StartYear that was entered in the message box, into the actual AutoFilter criteria. This code unselects ALL the years for some reason. I believe I'm not correctly referencing the StartYear in the AutoFilter. Thoughts?

 

[jski21]

Amazing what a short walk with some fresh air will do...

I believe this solves this issue, albeit a bit clunky maybe:

'Declared Variables
Dim StartYear As String


'Open Input Box and Ask User for a Start Year. Filter data on < StartYear,
Columns("O:O").Select
StartYear = Application.InputBox("Please provide a start year", "StartYear", Type:=1)


'Filter Column O based on Input Box criteria
ActiveSheet.ListObjects("PR07Table").Range.AutoFilter Field:=15, Criteria1 _
:="<" & StartYear, Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.SpecialCells(xlVisible).EntireRow.Delete
ActiveSheet.ListObjects("PR07Table").Range.AutoFilter Field:=15
Range("A2").Select


'Delete blank table rows
ActiveSheet.ListObjects("PR07Table").Range.AutoFilter Field:=15, Criteria1 _
:="", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.SpecialCells(xlVisible).EntireRow.Delete
ActiveSheet.ListObjects("PR07Table").Range.AutoFilter Field:=15
Range("A2").Select

 

[StephenCrump]

Well done!

The macro recorder helps with the syntax, but generates flabby and clunky code. A cleaner version:

Sub Test()

    Dim StartYear As String
    
    StartYear = Application.InputBox("Please provide a start year", "StartYear", Type:=1)
    
    Application.ScreenUpdating = False
    
    With Worksheets("Sheet1").ListObjects("PR07Table").DataBodyRange
        .AutoFilter Field:=15, Criteria1:="<" & StartYear
        On Error Resume Next
        Application.DisplayAlerts = False
        .SpecialCells(xlCellTypeVisible).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        .AutoFilter
    End With

    Application.ScreenUpdating = True

End Sub

 

[jski21]

Thanks Stephen. Your prompting in your initial response was integral in directing me down the right path to self-discover the answer. A good learning exercise for me. I' going to attempt the MATCH() option as I think it would be good to have both of these in my "hip pocket", so to speak. I'll report back.

Thanks again for the nudge!

jski

 

[jski21]

Hi Stephen and other members,

Discovered another way via looping to get this done. Not useful for large datasets (mine took 75 mins. to run) but thought I would post it here to support other inquirers who may have a use for it with smaller datasets:
-------------------------------------------------------------------------------------------------------------------------------------
'Open Input Box and Ask User for a Start Year. (LOOP THROUGH ROWS AND DELETE BASED ON < StartYear)
' Columns("O:O").Select

'Turn Off Screen Updating
Application.ScreenUpdating = False

'StartYear = Application.InputBox("Please provide a start year:", "StartYear", Type:=1)
'
' LastRow = Cells(Rows.Count, 15).End(xlUp).Row
' For i = LastRow To 2 Step -1
' If Cells(i, 15).Value < StartYear Then Rows(i).EntireRow.Delete
' Next i

'Turn Off Screen Updating
Application.ScreenUpdating = False
-------------------------------------------------------------------------------------------------------------------------------------

I am having a challenge with the MATCH() method. You would have to use Column O for the MATCH rather than Column H as there might not be a true year end date (12/31/XX) in that column, and yes, Column H is sorted in ascending order. With that, I thought perhaps an alternative might be to seek to find the first instance in Column O of whatever year is input in the message box, define that row number, offset it by -1 (this would essentially select the row with the LAST day of the year preceding the one you selected), expand that row range to Row 2, and then delete all the rows. Here's what I have so far:


'Open Input Box and Ask User for a Start Year. (LOCATE FIRST INSTANCE OF StartYear AND DELTE ALL OLDER ROWS)
Columns("O:O").Select


'Turn Off Screen Updating
Application.ScreenUpdating = False

StartYear = Application.InputBox("Please provide a start year:", "StartYear", Type:=1)

StartRow = Columns("O:O").Find(What:=StartYear, After:=[O1], LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
Set StartRow = n
Range("StartRow").Offset(-1, 0).Select

'Turn Off Screen Updating
Application.ScreenUpdating = False


This bombs off at Set StartRow = n as I'm missing an object and I'm not to certain how to proceed to finish at this point. Thanks all.

 

[Joe4]

It is very confusing what you are trying to do here:

StartRow = Columns("O:O").Find(What:=StartYear, After:=[O1], LookIn:=xlValues, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
Set StartRow = n

You seem to initially set StartRow equal to a row number (which would make it a number).
Then, on the next line, you seem to be trying to set StartRow to "n", which isn't designed anywhere.
So I have no idea what you are trying to do on that line.

Also, you only use the "Set" command when setting objects, like Ranges, Worksheets, Workbooks, etc.
You NEVER use "Set" when setting numeric or text variables (like a row number).

If n was a number that was calculated or set earlier, and you wanted to set "StartRow" equal to that number, you would just use:

StartRow = n

 

[jski21]

Thanks Joe4. Which is why I'm a bit lost. Tried to proceed in uncharted VBA territory for me. After setting StartRow to a row number by:

StartRow = Columns("O:O").Find(What:=StartYear, After:=[O1], LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

How would I then take that row number, movie it up one row, expand that range to row 2 and delete all the rows?

 

[Joe4]

How would I then take that row number, movie it up one row, expand that range to row 2 and delete all the rows?

Do you mean something like this:

If StartRow > 2 Then Rows("2:" & StartRow -1).Delete