Microsoft Office Forums

Microsoft Office Forums (https://www.msofficeforums.com/)
-   Excel (https://www.msofficeforums.com/excel/)
-   -   Deleting Multiple Rows (https://www.msofficeforums.com/excel/49478-deleting-multiple-rows.html)

JHalter 08-09-2022 04:27 PM

Deleting Multiple Rows
 
I copied over a list to excel which has data in row 1, then row 2 and 3 are blank, and data in row 4. This goes on for about 2000 rows. How do I quickly delete the 2 rows that are blank in between the cells that contain data?

Looks like this...

123
blank
blank
456
blank
blank
789
blank
blank

rollis13 08-10-2022 01:53 AM

Try this on a test file since the result depends on what data you have in your sheet (no blanks in data rows):

- press key F5 and click 'Special...' (or Go To Special from menu Home / Find & Select dropdown)
- select 'Blanks'
- OK
- use 'Delete Sheet Rows' (from menu Home / Delete dropdown)

ArviLaanemets 08-10-2022 05:11 AM

In case there is a column, which is never empty when there is anything entered into any other cell in same row:

Select the whole datarange, and from Home menu Sort & Filter. You get filter selection controls at top of datarange (in header row in case you had them, in 1st row of datarange otherwise);
Set the filter condition for column where always must be some value to '(Blanks)';
Select all filtered rows (row numbers at left of worksheet are colored blue), and delete them.

In case the is not such column (which must be always filled):
At right your datarange, into next column enter the formula like =COUNTA(A1:X1), which counts not empty cells in row, and copy this formula down for whole datarange;
Set Sort & Filter for datarange (the column with formula included);
Set the filter condition for added column to 0, and delete all filtered rows;
Delete the column with formula.


All times are GMT -7. The time now is 06:41 PM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft