Quick VBA help required please

[SV18]

Hello all,

I'm working on some address data, currently the addresses have blanks in some of the cells which end up splitting the address details.

I've written the following to remove the blanks, it isn't however working as sometimes there is more than one blank. So it doesn't line up as it should

For Each C in Range("b2:f6")

If C.value = "" then​

C.Delete shift:=xlToLeft​

End If​

Next C

Has anyone got any ideas of how I can do this?

EEID	Address1	Address2	Address3	Address4	Address5
8002	1 Road	Street	Town		Town
8003	2 Road	Street		Town	Town
8004	3 Road	Street			Town

<TBODY>
</TBODY>

 

[VoG]

Try

Sub test()
Dim i As Long, j As Long
For i = 2 To 6
    For j = 6 To 2 Step -1
        If Cells(i, j).Value = "" Then Cells(i, j).Delete shift:=xlShiftToLeft
    Next j
Next i
End Sub

 

[SV18]

Worked a treat, thanks for that VoG

 

[Peter_SSs]

You could also consider doing all the blanks directly and at once rather than looping through all the cells individually to check.

Sub Delete_Blanks()
  On Error Resume Next
  Range("A1").CurrentRegion.SpecialCells(xlBlanks).Delete Shift:=xlToLeft
  On Error GoTo 0
End Sub