0

I have four columns of data and 30,000 rows. There is one cell of data in each row, (A1, B2, C3, D4, A5, etc.,) the other three cells in each row are blank. I want to delete all the blank cells and shift all the remaining cells up. I can select 'blank' cells and delete > shift up, but with 30,000 rows Excel is not responding. I can do it column by column to reduce the load. Is there a better way?

Improve this question
1
  • Posted and 5 min later an idea. If it is always this organised. Cell E1 =A1 cell F1 =B2 so 2nd line data is now on F1 same row as E1. Cell G1 =C3, cell H1 D4. All four fields are now on one line. Select There four cell and the three empty ones below it. Double click green mark in the riht bottton to dra these down. You now have data on one line, three empty lines, second data line, 3 empty, tird data ... Halfway though the solution. And sort still can move all emty lines to top or bottom. Still not pretty, but less work than my first idea.
    – Hennes
    Commented Jun 5 at 16:05

1 Answer 1

Reset to default
2

Do this :

  • Select the range of cells
  • Press F5 and click Special… . Or go to the Home tab > Formats group and click Find & Select > Go to Special
  • In the Go To Special dialog box, select Blanks and click OK
  • Right-click any of the selected blanks and select Delete…
  • Depending on the layout of your data, you would probably choose Shift cells up
  • Click OK.

Take a backup of the spreadsheet before starting and verify well the result afterward.

For more details and screenshots see the article How to remove blank cells in Excel.

Improve this answer
2
  • Thanks, this is the correct way to do it and how I was doing it until my spreadsheet grew so long. Now that function is too much for Excel to execute in one go so I have to do it column by column. But I was looking for another way which would do it in one go.
    – Majo
    Commented Jun 5 at 17:16
  • I don't know of such a method (but it might exist). You could perhaps write a VBA macro, but I suspect that it would be even slower. You might perhaps look into a free Excel Alternative to find a faster product that is compatible with Excel.
    – harrymc
    Commented Jun 5 at 18:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .