Deleting empty rows

[JimH]

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?

[Bertie Bagshot]

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

[sen_edp]

check this out

http://www.cpearson.com/excel/deleti...eleteBlankRows

I think is what you need !!!

[Tikas A. Planck]

On 2002-05-04 06:33, sen_edp wrote:

check this out

http://www.cpearson.com/excel/deleti...eleteBlankRows

I think is what you need !!!

I think that JimH will be better advised to follow Bertie Bagshot's suggestion - he might then learn more about using Excel's built-in tools.

[NateO]

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, NateO

[ This Message was edited by: NateO on 2002-05-04 09:49 ]

[Tikas A. Planck]

On 2002-05-04 09:46, NateO wrote:
Don't think that's what JimH wanted for some reason:

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, NateO

[ 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.

[Nimrod]

[Nimrod]

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

[Tikas A. Planck]

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.

[Ivan F Moala]

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.