I have a macro to delete blank cells in each row and shift the next cell to the left. The code is not showing any errors, but does nothing.
As the number of columns can vary from row to row, I have defined the last row inside the For Each loop.
Help with correction gratefully received.
Sub RemoveBlankCells()
Dim rng As Range
Dim row As Range
Dim cell As Range
Dim lastCol As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("ABC")
For Each row In ws.Rows
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ws.Cells(1, lastCol)
For Each cell In rng.Cells
If cell.Value = "" Then
cell.Delete Shift:=xlToLeft
End If
Next cell
Next row
End Sub
Try this code. It limits it to just populated rows - assuming column A always has something in it.
Public Sub RemoveBlankCells()
With ThisWorkbook.Worksheets("ABC")
'Assume there's always something in column 1.
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, 1).End(xlUp).row
'Set reference to column A of the range to work on.
Dim SearchRange As Range
Set SearchRange = .Range(.Cells(1, 1), .Cells(LastRow, 1))
Dim rRow As Range
For Each rRow In SearchRange.EntireRow
'Find the last column number in the row.
Dim LastColumn As Long
LastColumn = .Cells(rRow.row, .Columns.Count).End(xlToLeft).Column
'Work backwards deleting cells - if you delete a cell in column 3 and move to column 4...
'column 4 has now become column 3 and that cell isn't checked.
Dim Counter As Long
For Counter = LastColumn To 1 Step -1
If .Cells(rRow.row, Counter) = "" Then .Cells(rRow.row, Counter).Delete Shift:=xlToLeft
Next Counter
Next rRow
End With
End Sub
Further reading:
With...End With statement