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

 

[jski21]

Do you mean something like this:

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

So simple but yet so far...

Yes Joe4. Just like that! Thank you for your guidance and help. Posting the full code below for future users:

'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

'Display message box and ask user for StartYear
StartYear = Application.InputBox("Please provide a start year:", "StartYear", Type:=1)

'Delete all rows prior to StartYear
StartRow = Columns("O:O").Find(What:=StartYear, After:=[O1], LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
If StartRow > 2 Then Rows("2:" & StartRow - 1).Delete

'Turn On Screen Updating
Application.ScreenUpdating = True

 

[Joe4]

You are welcome.

Glad you have it working now!

 

[jski21]

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

Hi Stephen,

Just giving you a knock as I mistakenly didn't hit the post reply to your message. Sorry about that. Just wanted to keep you in the loop as to the ideas passing through this entire thread. Thanks again for your help!

jski

 

[StephenCrump]

No problem at all. I'm glad we could help.