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

Conditional Processing During a Mail Merge

The Mail Merge capabilities can appear limited at first glance. One thing that is often overlooked (and which makes the ...

Discover More

Protecting Worksheets

Excel allows data protection for particular cells or a whole worksheet in a shared work environment. Here's how to apply ...

Discover More

Replacing Characters at the End of a Cell

The Find and Replace capabilities of Excel can come in handy, but they can't accomplish all your replacement needs. One ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Ignoring Paragraph Marks when Pasting

Paste information in a worksheet, and you may end up with Excel placing it into lots of different cells. If you want it ...

Discover More

Finding Unused Names

After months or years of naming things (such as cell ranges), you may find your workbook cluttered with a bunch of names ...

Discover More

Proper Case Conversion with Exceptions

The PROPER worksheet function allows you to change the case of text so that only the first letter of each word is ...

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 two minus 0?

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.