Consolidate a Column

**par0016** · 06-07-2012, 08:56 AM

I am trying to consolidate a column of say 10 rows down to the cells that do not contain a 0. I have a column that spits out either a name or a zero (depending on its date) and I want to consolidate this column to eliminate all zeros:

So Column A has the following in it:

Alpha
0
0
Charlie
0
Tango
Bravo
Delta
0
Foxtrot

I want column B to eliminate all 0's and Consolidate the column (while keeping the same order) to look like:

Alpha
Charlie
Tango
Bravo
Delta
Foxtrot

Any ideas would be greatly appreciated thanks

**jraj1106** · 06-07-2012, 09:01 AM

Hi

You can try this code:

Sub REMOVEZEROS()
    Dim rcnt As Long, cnt As Long
        
    rcnt = Range("A" & Rows.Count).End(xlUp).Row
    cnt = 1
    
    For i = 1 To rcnt
        If Range("A" & i).Value <> 0 Then
            Range("B" & cnt).Value = Range("A" & i).Value
            cnt = cnt + 1
        End If
    Next
End Sub

**brandonlaw** · 06-07-2012, 09:03 AM

There may be an easier/more formula-oriented way, but this will work and won't take long at all:

Copy all of column A to column B.
Use find/replace to delete all the 0s.
Use the info in this link to remove all blank cells: http://www.theexceladdict.com/_t/t031008.htm

**par0016** · 06-07-2012, 09:10 AM

I am looking for a way to do it with a formula and it needs to be self updating

**benishiryo** · 06-07-2012, 09:49 AM

hi par0016. phew i spent almost an hour thinkin of how to do this!
assuming data is from A2:A11, try:

=IF(ISERROR(INDEX($A$1:$A$11,SMALL(IF(--($A$2:$A$11<>0),ROW($A$2:$A$11)),ROW(A1)))),"",INDEX($A$1:$A$11,SMALL(IF(--($A$2:$A$11<>0),ROW($A$2:$A$11)),ROW(A1))))

after copying the formula in the formula bar, press CTRL + SHIFT + ENTER

let me know if it works