Delete Blank Cells in a Row
 

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

Delete Blank Cells in a Row
 

Hi Charles

You don't actually need a script.
Select you whole range of dataF5SpecialBlankOKright clickDeleteShift
cells left.

--
Regards
Roger Govier

"Charles" wrote in message
...
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

Delete Blank Cells in a Row
 

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

--
Gary''s Student - gsnu200837

Delete Blank Cells in a Row
 

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

--
__________________________________
HTH

Bob

"Charles" wrote in message
...
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

Delete Blank Cells in a Row
 

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

Regards

Charles

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200837

Delete Blank Cells in a Row
 

Hi Roger

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

Regards

Charles

"Roger Govier" wrote:

Hi Charles

You don't actually need a script.
Select you whole range of dataF5SpecialBlankOKright clickDeleteShift
cells left.

--
Regards
Roger Govier

"Charles" wrote in message
...
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

Delete Blank Cells in a Row
 

Hi Bob

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

Regards

Charles

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Charles" wrote in message
...
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