I need to remove empty lines from a very large spreadsheet
I need to remove empty lines from a very large spreadsheet
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.
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks