Deleting empty rows

JimH

Board Regular
Joined
Apr 21, 2002
Messages
94
Office Version
  1. 2010
Platform
  1. Windows
After using the Text Import Wizard to import a text file with multiple columns and thousands of lines, I end up with hundreds of empty rows throughout the worksheet (an empty row being one with no data in the cells of each column). As you can imagine deleting each blank row is a tedious exercise.

Is there a way to automate this process after importing the file?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
It doesn't need automation. It's quite simple (and quick) to do it manually :-

1. Insert a column before column A
2. Enter the following formula in A1 and fill down =IF(COUNTA(B1:IV1)<>0,"",1)
3.Go to Edit>GoTo>Special>Formulas>Numbers and click OK
4. Go to Edit>Delete>EntireRow and click OK
5. Delete column A
 
Upvote 0
Don't think that's what JimH wanted for some reason:

automate this process

I'm sure Bertie's works, but it's not instant gratification. Nothing wrong with learning VBA from Chip (this guy is good).

Nice call Sen! Have a good weekend all.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-04 09:49
 
Upvote 0
On 2002-05-04 09:46, NateO wrote:
Don't think that's what JimH wanted for some reason:

automate this process

I'm sure Bertie's works, but it's not instant gratification. Nothing wrong with learning VBA from Chip (this guy is good).

Nice call Sen! Have a good weekend all.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-04 09:49


I would suggest that JimH requested an automated way because he is not familiar enough with Excel's capabilities to do it manually by a simple and quick method.

This is well illustrated by his stating "As you can imagine deleting each blank row is a tedious exercise". Of course it is, but this is not the way to do it - the way to do it is by Bertie Bagshot's suggestion.

Also, if JimH really needs it to be automated, he can record his own macro by following Bertie B's method - this is what I was trying to put across about the benefits of becoming familiar with Excel's built-in tools rather than just relying on someone else to provide a macro.

I might also add that Chip Pearson's macro may not be as efficient as a macro based on Bertie B's method, given the large amount of data that JimH says he has to be processed.
 
Upvote 0
COUPLE MOUSE CLICKS WILL DO IT
I inport text on a regular basis and remove any blank rows with couple of mouse clicks by using the built in sort function. ... a couple of mouse clicks sorts all the blank rows to bottom of worksheet.
1)select columns you want to sort
2)select "data" on tool bar
3)select "sort" command on drop down menu
4)select "ok" ... DONE :)
 
Upvote 0
Nice one, Nimrod !

And of course, if the original sequence is required, just insert a column and fill it with sequential numbers before sorting. After sorting, delete the cells from this inserted column for the blank rows; re-sort by the inserted column; delete the column.
 
Upvote 0
On 2002-05-04 16:18, Tikas A. Planck wrote:
Nice one, Nimrod !

And of course, if the original sequence is required, just insert a column and fill it with sequential numbers before sorting. After sorting, delete the cells from this inserted column for the blank rows; re-sort by the inserted column; delete the column.

Thats exactly the way to do it....nice call,
of course if you do this often then I would
personally automate it, obviously depending
on how many times you would do this...ie
I could do n files with one click and continue
with other work...while this was processed.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top