Delete Blank Cells in a Row

[Charles]

I have 8000+ rows of data where each row has bank cells in random positions,
except for Column A which is aways populated.

Can someone help me with a script that will check each row, delete the bank
cell by moving the other cells in the row to the left.

Thanks

 

[Roger Govier]

Hi Charles

You don't actually need a script.
Select you whole range of data>F5>Special>Blank>OK>right click>Delete>Shift
cells left.

 

[Gary''s Student]

Sub squeezeBlanks()
Dim n As Long, i As Long
Dim j As Long
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
k = Cells(i, Columns.Count).End(xlToLeft).Column
For j = k To 2 Step -1
With Cells(i, j)
If IsEmpty(.Value) Then
.Delete Shift:=xlToLeft
End If
End With
Next
Next
End Sub

 

[Bob Phillips]

Untested

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = LastCol To 2 Step -1

If .Cells(i, j).Value = "" Then

.Cells(i, j).Delete Shift:=xlToLeft
End If
Next j
Next i
End With

End Sub

 

[Charles]

Hi
This works very well, but it is looping at the "End If" statement

Regards

Charles

 

[Charles]

Hi Roger

Thanks, a neat and simple solultion. I must make more use of F5.

Regards

Charles

 

[Charles]

Hi Bob

This also works. Thank you, all all the experts who reply so quickly and
concisely.

Regards

Charles