Forum Discussion
how to delete every other line in excel work sheet
how do i delete every other line in a worksheet?
5 Replies
A very roundabout way I used to do it was just put 1, 2, 3… down the side, then filter for either odd or even numbers. That way you can delete all the visible rows in one go and you’re left with every other line. Glad to have learned better methods here! 😀
In addition to the suggested options with formulas, here is an inserted file with VBA where you select all the lines you want to keep and the macro does the rest (deletes).
The VBA code in the attached Workbook works in all modern versions of Excel that support macros, Excel 2016, Excel 2019, Excel 2021, Excel 365 / Microsoft 365.
It will not work in Excel for the web, because the online version doesn’t support running VBA macros. It also works in both Windows and Mac versions of desktop Excel, though on Mac the interface for running macros is slightly different.
The only requirements: Macros must be enabled in your Excel workbook.
A user-friendly macro where you can select the rows to keep via a dialog🙂
This was a lifesaver!
You might enter the formula =ISODD(ROW(A1:A1000)) in a cell in row 1 of an empty column.
You can then filter on TRUE or on FALSE depending on which rows you want to delete, then delete the visible rows.
Are the lines you want to delete blank lines--no data at all? You could use the FILTER function to extract rows that contain stuff and set the result as the new data set.
It would help, though, if you described the situation a bit more fully, since other wise you're asking us to guess what's going on, what is characteristic of the rows to be retained, and/or of the rows to be deleted.
