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

Converting to Octal

If you need to do some work in the base-8 numbering system (octal), you'll love two worksheet functions provided by Excel ...

Discover More

Comments Only Visible When Hovering Over a Word or Phrase

The comment feature of Word allows you to easily attach comments to words or phrases in your document. How those comments ...

Discover More

Previewing Your Files

Want to get a glimpse of what is inside a file without evening opening it? Drive provides two distinct ways of peeking ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Deleting Rows before a Cutoff Date

If you are using Excel to work with data that is date-centered, you may want to delete some of the data before a specific ...

Discover More

Forcing Editing to Be Done in a Cell

Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only ...

Discover More

Converting Forced Text to Numbers

If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those ...

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 three more than 4?

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.