excelvbacell

Macro to delete blank cells in row


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

Solution

  • 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