Deleting All Rows Except for the End of Month

Written by Allen Wyatt (last updated June 6, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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:


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:


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


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 Microsoft 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. ...


Avoiding the "Check Remainder" Dialog Box

Need to check grammar using a macro? Word includes a couple of different ways to perform the check, and what you see ...

Discover More

Searching and Replacing Graphics

Got a bunch of graphics in a document that need replacing? (Perhaps you need to replace an old logo with a new one.) Word ...

Discover More

Counting Words in Comments

Word makes it easy to calculate the number of words in a document. If you want to count words only in your comments, then ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Controlling the Automatic Copying of Formulas

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 More

Importing a Subset of Records

If you only want to import a portion of whatever records are in a text file, Excel provides a number of ways you can ...

Discover More

Getting Rid of Everything Except Numbers

Got some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with ...

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 nine minus 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

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.