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

Opening a Template

If you have a template stored on disk, you can open it and make changes to it just as you do other documents. This tip ...

Discover More

Lotus Grouped Worksheets

Not all spreadsheet programs are created equal; there are some things that can be done in others that can't be done in ...

Discover More

RGB Values for Automatic Colors

When you create a chart, Excel automatically assigns different colors to the various data series in the chart. At some ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Setting a Default for Shifting when Inserting

When you insert cells into a worksheet, Excel needs to know which direction it should shift the displaced cells. If you ...

Discover More

Entering Characters with Diacritical Marks

Entering characters that use diacritical marks is easy as pie in Word and some other programs. Not so in Excel, it takes ...

Discover More

Ranges on Multiple Worksheets

Referring to a range of cells on the current worksheet is easy. But what if you want to refer to a range on a number of ...

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 four less 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.