+ Reply to Thread
Results 1 to 4 of 4

remove blank lines from an entire spreadsheet

  1. #1
    mdeanda
    Guest

    remove blank lines from an entire spreadsheet

    I need to remove empty lines from a very large spreadsheet

  2. #2
    Registered User
    Join Date
    04-20-2005
    Posts
    2
    I found this while searching for an answer to a question. Hope it helps.

    Suppose we have a range of cells called BlanksRange. Some of the cells in this range contain data, and others are blank. We are interested in extracting the non-blank entries from this range, and returning them to the first rows of another range.

    Create a range, with the same number of rows as BlanksRange, and starting in the same row as BlanksRange, called NoBlanksRange. Enter the following Array Formula in the first cell of NoBlanksRange, and then use Fill Down to fill out the range:

    =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
    COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
    (IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
    ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

    The first N rows of NoBlanksRange will contain the N non-blank cells of BlanksRange. Note that this is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }.

    Note that if you do not use named ranges and enter the actual cell references, you must use absolute cell references (e.g., $B$1) rather than relative cell references (e.g., B1).



    Example


    As you can see here, the values in BlanksRange
    are shifted up, as the blank entries are removed.






    Download a workbook illustrating these formulas.

  3. #3
    Ashish Mathur
    Guest

    RE: remove blank lines from an entire spreadsheet

    Hi,

    Select the sheet area containing the text and blank cells. Do the following

    1. Ctrl+G
    2. Go to Special
    3. Select blanks
    4. Click OK

    It will highlight all the blank rows in Blue. Now do the following

    1. Alt E+D+R (To deletye the rows)

    Regards,

    Ashish Mathur






    "mdeanda" wrote:

    > I need to remove empty lines from a very large spreadsheet


  4. #4
    Andri
    Guest

    RE: remove blank lines from an entire spreadsheet

    hope it will help, got from this group also...
    put as macro:
    Sub Macro1()
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    SkipBlanks _
    :=False, Transpose:=False
    End Sub

    "mdeanda" wrote:

    > I need to remove empty lines from a very large spreadsheet


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1