Macro to Delete Empty Cells -> Shift Left

[sthrncaliguy]

I am in need of a macro that will look through a pre set range of cells, say B:B for example, and for each cell that is blank, it will delete and shift cells left.

I know this is probably simple, just need some help with the code.

Thanks!

 

[id107]

this should do it.

Sub test()
 
    Dim i As Long, LastRow As Long
 
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 
    For i = 1 To LastRow
        If Range("B" & i).Value = "" Then
            Range("B" & i).Delete Shift:=xlToLeft
        End If
    Next i
 
End Sub

 

[john456852]

this should do it.

Sub test()
 
    Dim i As Long, LastRow As Long
 
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 
    For i = 1 To LastRow
        If Range("B" & i).Value = "" Then
            Range("B" & i).Delete Shift:=xlToLeft
        End If
    Next i
 
End Sub

How to Shift Cells Up? Do u use xlToUp or xlUP?