Barry has a large worksheet containing several thousand rows of data. Column B contains a date, and he needs to delete all the rows in which the date in column B is earlier than a specific cutoff date. Barry wonders about the easiest way to do this for so much data.
This is rather easy to do, with the approach you use dependent on how often you need to do it and how you want to work with your data. If you don't care what order your data is in, then the easiest method is what I refer to as the "sort and delete" method:
This works great if you only need to perform that task once in a while and if you don't mind the rows in the data being reordered. If reordering is a problem, then you may want to add a column to your data and fill that column with values from 1 to however many rows of data you have. You can then perform the "sort and delete" method, but afterwards resort your data based on the values in the column you added.
Of course, you could also use a "filter and delete" method, which will leave your data in its original order without the need of a helper column:
If you need to perform the task of removing rows often, then you won't be able to beat the convenience of using a macro. The following macro assumes that you've placed the cutoff date into cell K1. It grabs this date and then looks at each row in your data, deleting any rows that are before this cutoff date.
Sub DeleteRowsBeforeCutoff() Dim LastRow As Integer Dim J As Integer Application.ScreenUpdating = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row For J = LastRow To 1 Step -1 If Cells(J, 2) < [K1] Then Cells(J, 2).EntireRow.Delete End If Next J Application.ScreenUpdating = True End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1566) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When you add a new row of data to the bottom of the data of a worksheet, Excel may (or may not) copy formulas downward to ...
Discover MoreDo you need to use symbols frequently in your Excel data? The common way to insert them is by using the Symbol dialog ...
Discover MoreWhen you import information originating in a different program, Excel may not do the best job at figuring out what ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-02-15 11:58:58
J. Woolley
For a similar Tip, see
https://excelribbon.tips.net/T010783_Deleting_Old_Data_from_a_Worksheet.html
2022-02-05 18:36:46
Peter
Oops
Columns(2) should be Columns(1) in my example (where the field being tested is in column A). Just need to make sure $A$ matches (1)
Peter
2022-02-05 03:11:15
Peter
If you try to automate the "filter and delete" method, you would use something like the following.
The resulting visible rows will not be contiguous so you have to work with areas. This is not a problem - you just cycle through and delete all the rows in each area. Area 1 is the column heading and is not deleted.
Columns(2).AutoFilter Field:=1, Criteria1:="<0"
For nA = nAreas To 2 step -1 ' delete areas from the bottom.
Set aa = Columns(1).SpecialCells(xlCellTypeVisible).Areas(nA) ' next area to delete
Set rr = Rows(Replace(aa.Address, "$A$", ""))
rr.Delete Shift:=xlUp
Next nA
Columns(1).AutoFilter
2021-07-08 16:35:43
Philip
One note of caution with the macro approach : be aware that “undo” won’t be possible after executing the macro …
2021-01-01 23:39:57
dremmarket
"What’s up it’s me, I am also visiting this web page daily,
this site is in fact pleasant and the users are
genuinely sharing pleasant thoughts."
Regards: <a href="https://dreammarket.link">Dream market</a>
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments