Deleting All Rows Except for the End of Month

by Allen Wyatt
(last updated June 6, 2020)

David has a worksheet that includes just two columns: a date and a portfolio value for each date. The worksheet has rows for every trading day from January 1999 to the present. David needs to delete all the rows except those that are for the last trading day of each month. He tried filtering, but that didn't help, so he is confused on the best way to delete the unneeded rows.

In providing ways to tackle this problem, one of the keys in the question that I'm going to focus on is that David said his data "has rows for every trading day." To me, this means that some dates (non-trading days) are not included in his data. This is key because it means we don't have to come up with a solution that, before deciding whether to keep a row or not, determines if a date is a trading day.

This actually makes the job much easier. Now we can simply find, from the dates in column A, those rows that actually contain the last (or "highest") date in any given month. I will focus first on a manual approach that relies on a helper column. Since David said his data consists of only columns A (date) and B (value), I will suggest using column C as the helper column. (I'm also going to assume that row 1 has column headers in it, and the real data starts in row 2.)

Making sure that your data is sorted so that the dates are in ascending order, put the following formula into cell C2:

=IF(DAY(A3)<DAY(A2),"EOM","X")

Copy this formula down for all of your data, and you are essentially done. You can now, if desired, use filtering based on column C. If you filter so that only rows containing "EOM" are shown, then you have your final values for each month. If you filter so that only rows containing "X" are shown, then you could delete those rows, remove the filter, and have only rows with the end-of-month values in your data.

As is often the case, there are a plethora of formulas that you could use in column C instead of the one I suggest. I suggested this one, though, because it makes a very simple comparison that will always be testable—whether the day "drops" in value in the row following the current one. In every possible scenario, this will only be true at the end of a month. So, that row is marked with "EOM" and the rest with "X."

I should point out that if you choose to use a different formula, make sure it is one that doesn't test if the date in column A is the last day of the month. Why? Because it may not be—remember that in David's data, column A contains the dates of trading days, and it is very possible that the last trading day of a month may not fall on the last day of the month. (Weekends and holidays, in other words, are excluded, by definition, from David's data.)

If you are using Office 365 (or what Microsoft, these days, is referring to as Microsoft 365), there is also a way you could pull just the month-end dates and their values. Let's assume that your data is in A2:B5000. (Remember that A1:B1 contains column headers.) Put the following formula into cell E2:

=FILTER(A2:B5000,DAY(A3:A5001)<DAY(A2:A5000),1)

That's it; a single formula in a single cell. You may have to format column E to display dates properly, but this usage of the FILTER function does the same comparison already discussed and pulls just the month-end dates and values. (This is quite slick, if you think about it.) Remember, though, that it will only work in Office 365; it won't work in Excel 2019, Excel 2016, or any earlier version of the program.

If you prefer a macro-based approach, then the following short macro will do the trick:

Sub DelRows()
    Dim LastRow As Long
    Dim J As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastRow = LastRow - 1
    For J = LastRow To 2 Step -1
        If Month(Cells(J, 1)) = Month(Cells(J + 1, 1)) Then
            Rows(J).EntireRow.Delete
        End If
    Next J
End Sub

The macro determines the last row in the worksheet (stored in the LastRow variable) and then uses a For...Next loop to step through the rows backwards. If the month of the current row is equal to the month of the row following, then the row is deleted. Note that the macro decrements LastRow before jumping into the For...Next loop. This is done because the assumption is that the last row of data will always be the last trading day of your data, thus it always stays with the stripped-down data.

This macro can be slow to run, as it is deleting most of the rows in the worksheet, one by one. When it is complete, however, you will only have your month-ending data remaining.

One final note—the approaches used in this tip are, with the exception of the FILTER function, destructive of your data. When you use them, data in your worksheet will be forever lost. This means you should think twice (or thrice) before you run them on anything except a copy of your original data.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13768) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Extending a Paragraph into the Left Margin

Word allows you to format a paragraph so that it extends into the left margin of the document. This is done by setting a ...

Discover More

Jumping to a Relative Line Number

As you navigate through a document, you may have a need to move forward or backward a specific number of lines. This is ...

Discover More

Backing Up Your Custom Dictionaries

When you work with the spelling checker quite a bit, you eventually end up with a sizeable custom dictionary. You might ...

Discover More

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!

More ExcelTips (ribbon)

Deleting Stubborn Links

Deleting unwanted links in a workbook can be a challenge, particularly if you are not sure how those links got there. ...

Discover More

Changing References in a Lot of Defined Names

Need to change some cell references in your defined names? Changing one or two is easy; changing dozens is a good deal ...

Discover More

Copying Rows between Worksheets Based on a Text Value

Want to move data from one worksheet to another based on a text value in a column. There are a couple of ways you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 5?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.