# How to Remove Blank Cells-Excle Formula



## Nikil (Apr 16, 2007)

Hi 
I have two columns of data... say a1:b20..out of it, i have some blank rows which should be elimanate by Excel Formula except one . i.e at any cost the data should be separated by one blank row. Not with multiple rows.
Ex.

row no A B
1 COL1 COL2
2 20 YYY
3 30 XXX
4 (BLANK) (BLANK)
5 (BLANK) (BLANK)
6 HDR1 HDR2
7 XY1 ABC
8 XZ2 BBC
9 XT4 AB22
10 (BLANK) (BLANK)
11 (BLANK) ( BLANK)

.. and so on...!

In the above example, two sets of data (data is different) is separating by some blank rows. i.e two here. If any one can write a formula such that, the two sets of data sould be separate by only one row.

i need to achive this only with Excel Formulas. Please do not use VBA at any stage.
Please help me on this if anyone knows how to do it?

i.e the output should be something like...

row no A B
1 COL1 COL2
2 20 YYY
3 30 XXX
4 (BLANK) (BLANK)
5  HDR1 HDR2
6 XY1 ABC
7 XZ2 BBC
8 XT4 AB22
9 (BLANK) (BLANK)
10


----------



## Charles_Bukowski (May 3, 2007)

What does your function look like that inputs this data... if its not a function then what is causing the two blank records? (i.e. Is this data imported from somewhere else?)


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

In C1 enter a column header, i.e. *TempHeader*. In column C2 enter the formula...


```
=AND(A1="",B1="",A2="",B2="")
```
Copy down to the last row you desire (select the cell, grab the lower right-hand corner, should be a black *+* sign, drag down where desired and let go).

Autofilter column 3 for TRUE
If there are values present (else do not do the following steps):
Select the visible cells in column C (not including the header)
Hit *Shift + Spacebar*
Hit *F5*, click *Special...*
Select *Visible cells only*, click *Ok*
Right click any row header that is visible, select *Delete Row*
Delete column C

You can do this in about 0.1 seconds with VBA (I know you said you didn't want it) by doing the same thing...


```
Sub DeleteSingleBlanks()
    Dim ws As Worksheet, LastRow As Long
    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Columns(3).Insert
    With ws.Range("C2:C" & LastRow)
        .Formula = "=IF(AND(A2="""",B2="""",A1="""",B1=""""),TRUE,FALSE)"
        .Value = .Value
    End With
    ws.Range("C1").Value = "TmpHdr"
    ws.Columns(3).AutoFilter field:=1, Criteria1:="=TRUE"
    With ws.Range("C2:C" & LastRow)
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    ws.Columns(3).Delete
    ws.AutoFilterMode = False
End Sub
```
HTH


----------



## bomb #21 (Jul 1, 2005)

Just a variation of Zack's for fun.

In C1 enter a column header, i.e. TempHeader. In column C2 enter the formula:

=IF(COUNTA(A1:B2)=0,"",1)

Copy down to the last row you desire.

Select all of column C (CTRL+SpaceBar).

Press F5 -- click "Special" -- click the radio button for "Formulas" -- check "Text" *only* -- click OK.

Select "Delete" from the Edit menu. Choose "Entire Row", click OK.


----------



## Zack Barresse (Jul 25, 2004)

Your formulas going to be faster than mine also Andy.


----------



## Nikil (Apr 16, 2007)

Sorry for late response. Thank you very much for your afford. The solution shown is not straight forward for me. It has involved some manual interventions. i.e. Deleting rows . etc.
My requirement is basically reflect source data in target sheet with a blank row separated by each set of information. User never do work like deleting rows etc.. I want a formula to achieve whole thing.
I came up some work..It is fulfilling my requirement with some intermediate work. 
At some temporary place.. say E2 in Sheet1
Put this formula at row number twoCopy the same till end or rows.
=IF(OR(A3="FirstCol",A3="T1")," ",IF(ISBLANK(A2),IF(AND(ISBLANK(B2),ISBLANK(C2)),""," "),A2))
If you have more columns, (in my case two column) repeat the same in next column as well.
Ex. Put this formula in F2 in Sheet1 and copy the same till end of data.
=IF(OR(B3="SecondCol",B3="T2")," ",IF(ISBLANK(B2),IF(AND(ISBLANK(A2),ISBLANK(C2)),""," "),B2))
You will get some intermediate result with the above formulas.
Now put below formula in target column in Sheet2.
Ex. Sheet2!A1 , put the below formula
=IF(ROW()-ROW(E$1:E$30)+1>ROWS(Sheet1!E$1:E$30)-COUNTBLANK(Sheet1!E$1:E$30),"",INDIRECT(ADDRESS(SMALL((IF(Sheet1!E$1:E$30<>"",ROW(Sheet1!E$1:E$30),ROW()+ROWS(Sheet1!E$1:E$30))),ROW()-ROW(E$1:E$30)+1),COLUMN(Sheet1!E$1:E$30),4,,"Sheet1")))
This Array Formula..So press Ctrl+Shift+Enter

Repeat the above for second column as well..Ex. In Sheet2! B1, put the below formula
=IF(ROW()-ROW(F$1:F$30)+1>ROWS(Sheet1!F$1:F$30)-COUNTBLANK(Sheet1!F$1:F$30),"",INDIRECT(ADDRESS(SMALL(
(IF(Sheet1!F$1:F$30<>"",ROW(Sheet1!F$1:F$30),ROW()+ROWS(Sheet1!F$1:F$30))),
ROW()-ROW(F$1:F$30)+1),COLUMN(Sheet1!F$1:F$30),4,,"Sheet1")))
This Array Formula..So press Ctrl+Shift+Enter
Hope, some can take it forward and achieve this without any temporary work.. Thank you.
Rgds.


----------



## bomb #21 (Jul 1, 2005)

Nikil said:


> The solution shown is not straight forward for me. It has involved some manual interventions. i.e. Deleting rows . etc.


You are correct that any solutions provided involve some manual intervention. However, it was not specified that a completely automatic solution was required. 

Using your original example:

row no A B
1 COL1 COL2
2 20 YYY
3 30 XXX
4 (BLANK) (BLANK)
5 (BLANK) (BLANK)
6 HDR1 HDR2
7 XY1 ABC
8 XZ2 BBC
9 XT4 AB22
10 (BLANK) (BLANK)
11 (BLANK) ( BLANK)

Let's assume that this is on "Sheet1" from A1 to B10 (i.e. "row no A B" and "1 COL1 COL2" are ignored).

Enter this in Sheet2!A1 & copy it down:

*=COUNTA(Sheet1!$A$1:A1)*

Enter this in Sheet2!B1 & copy it down:

*=INDIRECT("Sheet1!A"&MATCH(ROW(),A:A,0))*

Enter this in Sheet2!C1 & copy it down:

*=INDIRECT("Sheet1!B"&MATCH(ROW(),A:A,0))*

NB: _this_ method assumes that the entries in Sheet1!A:A are unique and will strip out *all* blanks. Hope it helps.


----------



## Zack Barresse (Jul 25, 2004)

Nikil said:


> I want a formula to achieve whole thing.


That is not going to happen. It just doesn't work like that. Sorry.



Nikil said:


> Hope, some can take it forward and achieve this without any temporary work.


Not going to happen either.

Post a representative sample of your work, or even your work itself. Tell us EVERYTHING that is involved with your project/file(s). We cannot help you if you only tell us little pieces at a time.


----------



## Nikil (Apr 16, 2007)

Sorry Guys! I understand , i missed somewhere to communicate well.
Sorry for that..I'm bad in english. Sorry!!
Whatever Im exploring with excel formulas now a days, is all belongs to Excel Services which we are currently working. I need to more relies on Excel formulas to achieve lot of common things in excel. I can not use any VBA code at any stage. Whatever sample I am posting is some dummy data. it exactly reflects the values of original data. Even I too dont know what real data there is...!!
Andy your formula is very simple & nice and it eliminates blank cells. Good Logic.
Thanks a lot.
Thanks & Rgds


----------

