loop through rows removing empty cells

Question

Hi Folks,

I have a spreadsheet with a list of customers in column A.  Columns B - I'm looking for a way to accomplish the following...

Beginning with the first row column B, look across the row and delete any empty cells (shift left) prior to the first occurrence of data.  Every row in column A will be populated to the end of the list.  The data will begin a variable number of columns across for each row.  Basically I'm looking for a programmatic way to condense each row to remove all empty cells prior to the data.

Answer 1

found it...

Sub TruncateToWeek1()

    Dim r As Range, c As Range, i As Long

    Application.ScreenUpdating = False

    With ActiveSheet

        Set r = ActiveSheet.Range("B1:AA1000")

    End With

    With r.Worksheet

        For i = r.Rows.Count To 1 Step -1

            For Each c In r.Rows(i).Cells

                If c.Value <> 0 Then

                    If c.Column > r.Cells(1, 1).Column Then

                        .Range(r.Cells(i, 1), c.Offset(0, -1)).Delete xlToLeft

                    End If

                    Exit For

                Else

                    If c.Address = r.Cells(i, r.Columns.Count).Address Then

                        c.EntireRow.Delete xlUp

                    End If

                End If

            Next c

        Next i

    End With

    Application.ScreenUpdating = True

End Sub