Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Deleting empty rows

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2

    Join Date
    Feb 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    check this out

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

    I think is what you need !!!

  4. #4

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •