Delete empty cells and move to left

[S.Daniels]

Hi
In column A is a list of machine numbers. Column C:CZ there are tool numbers. I need to push all of the data in each row into consecutive cells. ( Delete all of the cells that are not numbers) the data is coming from a pivot table using an if formula =IF(E6,$E$5,"") then copy and pasted to another sheet. So I'm not sure if the blank cells are truly empty.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">304028</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">308025</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">308026</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">308541</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">310030</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">310033</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">311007</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">311008</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">602D88</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">311012</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">99094 M1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">311013</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">311014</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">311015</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">601206 M2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">311021</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">97201</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">311022</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">311023</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">311024</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">311027</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">311029</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">311032</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">311034</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">311035</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">311036</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">602D89</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">311037</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">311038</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">601168 / 601169</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">311042</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">311043</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">47536</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">311044</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">311045</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

 

[p45cal]

If they are true blanks:

Select columns C:CZ
Press F5
Click Special…
Choose Blanks
Click OK
Right-click one of the selected cells
Choose Delete…
Choose Shift cells left
Click OK.

If they are not… I'm working on it.

 

[p45cal]

If they are not… right, got it:

Select Columns C:CZ
Press Ctrl+H to bring up the Find & Replace Dialogue
Make sure there's nothing in the Find field
Click Find all
(Clicking Replace All will not do the necessary at this point!)
Now press Ctrl+A on the keyboard
Click Close
Right-click one of the selected cells
Choose Delete…
Choose Shift cells left
Click OK.

 

[S.Daniels]

The second example works but now how do I put this into a macro.

 

[p45cal]

You don't, you have to do it a different way, such as:

Sub blah()
Dim rng As Range
With ActiveSheet
    For Each cll In Intersect(.UsedRange, .Range("C:CZ")).Cells
        If Len(cll.Value) = 0 Then Set rng = Union(cll, IIf(rng Is Nothing, cll, rng))
    Next cll
End With
rng.Delete xlShiftToLeft
End Sub

Dpending on how big your range is and which version of Excel you're using this might do it…
BUT I would consider removing formulae first anyway with a copy/paste-values in situ first. If you wanted to do this in a macro too then:

Sub blah()
Dim rng As Range
With ActiveSheet
    With Intersect(.UsedRange, .Range("C:CZ"))
        .Value = .Value
        For Each cll In .Cells
            If Len(cll.Value) = 0 Then Set rng = Union(cll, IIf(rng Is Nothing, cll, rng))
        Next cll
    End With
End With
rng.Delete xlShiftToLeft
End Sub

 

[AuditAilsa]

I know it's years later, but your help is still valid - much appreciated - many hours saved.

 

[echung379]

Thank you very much. I registered for this forum just because your post helped me save so much time.

 

[apoxy123]

You don't, you have to do it a different way, such as:

Sub blah()
Dim rng As Range
With ActiveSheet
    For Each cll In Intersect(.UsedRange, .Range("C:CZ")).Cells
        If Len(cll.Value) = 0 Then Set rng = Union(cll, IIf(rng Is Nothing, cll, rng))
    Next cll
End With
rng.Delete xlShiftToLeft
End Sub

Dpending on how big your range is and which version of Excel you're using this might do it…
BUT I would consider removing formulae first anyway with a copy/paste-values in situ first. If you wanted to do this in a macro too then:

Sub blah()
Dim rng As Range
With ActiveSheet
    With Intersect(.UsedRange, .Range("C:CZ"))
        .Value = .Value
        For Each cll In .Cells
            If Len(cll.Value) = 0 Then Set rng = Union(cll, IIf(rng Is Nothing, cll, rng))
        Next cll
    End With
End With
rng.Delete xlShiftToLeft
End Sub

How do I do this, but also keep my formulas when I update my pivot table and select a new product/new set of numerical values, then run the macro again for these new values to delete blanks?