Better VBA way to compress list

Question

Have a few questions about the following code that compressed and reformats a list.

1. I set variable endIndicator as a temporary marker for the end of the list. Would it be better to just continually check for the current end of the list via my ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row - 1?

2. I tried a With ActiveCell.Offset(rowOffset) statement in the delete loop which gives me a

   run time error 424 Object Required

   on the second iteration of the loop. I gather this has to do with the destruction of the previous row. Is there an appropriate With statement to use for this loop?

3. Similarly, I tried a With ActiveCell.EntireColumn for the last three statements and get the same result. I assume for the same reason. Is there an appropriate solution?

Here's the code

Option Explicit

Sub Condense1()    
'Purpose : Condense list by removing unwanted rows    
'Requires: Column B row verbiage    
'          Column A row blank for unwanted row    
'Returns : Single compressed column of values wanted    

    Dim endIndicator As String
    Dim rowOffset As Long

    Worksheets(1).Activate          'Select Sheet
    Range("A1").Select              'Set offset base

    endIndicator = "zzzendozx"      'Assign unique value unlikely to be duplicated
                                    'Find last used row
    rowOffset = ActiveSheet.UsedRange.Rows.Count _
        + ActiveSheet.UsedRange.Rows(1).Row - 1
                                    'Temporarily mark next row as loop terminator
    ActiveCell.Offset(rowOffset, 0).Value = endIndicator

    rowOffset = 0                   'Reset offset pointer

                                    'For each row from top to loop terminator
    Do While ActiveCell.Offset(rowOffset).Value <> endIndicator
                                    ' Delete rows whose column "A" is empty
        If Len(ActiveCell.Offset(rowOffset).Value) < 1 Then
            ActiveCell.Offset(rowOffset).EntireRow.Delete
        Else
            rowOffset = rowOffset + 1   'Otherwise prepare to look at next row
        End If
    Loop
    ActiveCell.Offset(rowOffset).EntireRow.Delete   'Remove loop terminator row
    ActiveCell.EntireColumn.Delete                  'Remove Column A
    ActiveCell.EntireColumn.Font.Size = 14          'Set Font
    ActiveCell.EntireColumn.AutoFit                 'Set optimum column width

End Sub

Answer 1

This may be of some help:

Sub Kompressor()
    Dim nLastRow As Long, nFirstRow As Long, i As Long
    Set r = ActiveSheet.UsedRange

    nLastRow = r.Rows.Count + r.Row - 1
    nFirstRow = r.Row

    For i = nLastRow To nFirstRow Step -1
        With Cells(i, 1)
            If .Value = "" Then .EntireRow.Delete
        End With
    Next i
End Sub

Note we run the loop backwards.
The code will run slightly faster if we delete only one time.