Return to Answer

• h is initialized at an arbitrary value of 1000. See this SO answer for a very detailed explanation of various ways to locate the last row that contains data on a worksheet. For example this would set h to the last row with data in column A:

   h = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
  

  By working off the actual last row, you avoid extraneous iterations later, and avoid having to modify your code if/when the worksheet starts containing more than 1000 rows.

• We Select the first cell in the 999th row, and then start looping from 1 to 999.

  If the ActiveCell is empty, we move up a row; else, we... wait a minute... we reassign the loop variable back to 999??

  It's pretty hard to understand what's happening here, and more importantly, why - and explaining why something is happening is exactly what comments are for!

   'this loop sets n to the last row with data (right?)
  

• h is initialized at an arbitrary value of 1000. See this SO answer for a very detailed explanation of various ways to locate the last row that contains data on a worksheet. For example this would set h to the last row with data in column A:

   h = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
  

  By working off the actual last row, you avoid extraneous iterations later, and avoid having to modify your code if/when the worksheet starts containing more than 1000 rows.

• We Select the first cell in the 999th row, and then start looping from 1 to 999.

  If the ActiveCell is empty, we move up a row; else, we... wait a minute... we reassign the loop variable back to 999??

  It's pretty hard to understand what's happening here, and more importantly, why - and explaining why something is happening is exactly what comments are for!

   'this loop sets n to the last row with data (right?)
  

We want to delete rows where column A is empty. By adapting this answer to Conditionally deleting rows in a worksheet, we can enormously simplify what's going on here - the key being this little function:

We want to delete rows where column A is empty. By adapting this answer to Conditionally deleting rows in a worksheet, we can enormously simplify what's going on here - the key being this little function:

Public Sub CleanUpActiveSheet()
    
    Dim target As Worksheet
    Set target = ActiveSheet
            
    Dim lastRow As Long
    lastRow = target.Range("A" & target.Rows.Count).End(xlUp).Row

    Dim toDelete As Range
    
    Dim currentRow As Long
    For currentRow = 1 To lastRow
        If IsEmpty(target.Cells(currentRow, 1)) Then
            Set toDelete = CombineRanges(toDelete, target.Cells(currentRow, 1))
        End If
    Next
    
    If Not toDelete Is Nothing Then toDelete.EntireRow.Delete
    
End Sub

Public Sub CleanUpActiveSheet()
    
    Dim target As Worksheet
    Set target = ActiveSheet
            
    Dim lastRow As Long
    lastRow = target.Range("A" & target.Rows.Count).End(xlUp).Row

    Dim toDelete As Range
    
    Dim currentRow As Long
    For currentRow = 1 To lastRow
        If IsEmpty(target.Cells(currentRow, 1)) Then
            Set toDelete = CombineRanges(toDelete, target.Cells(currentRow, 1))
        End If
    Next
    
    If Not toDelete Is Nothing Then toDelete.Delete xlUp
    
End Sub