VBA code to delete rows in Excel

[JustMyAlias]

Seems like this would be super simple, but I'm not seeing my issue...
I have a spreadsheet that is locked. This is only bc the formulas have been mistakenly changed or incorrect by adding or deleting rows willy nilly.
I have created it with the max # of rows that should ever be needed, but typically they aren't all used.
After the spreadsheet is populated, I want VBA to remove the unused rows.
I will also do this for a second range (A35:E40).
Currently, only some of the rows are being deleted, instead of all blank rows in the range.
TIA!

This is the code:

Sub DeleteExtraRows()

' DeleteExtraRows - to remove any extra/blank rows (10-26) on worksheet

    Dim strPassword As String
    Dim iRange As Range
            
    strPassword = "password"
    
    'remove password protection
    ActiveSheet.Unprotect password:=strPassword
    
    'User warning
    MsgBox "This will delete the blank rows. Are you sure you want to delete?", vbOKCancel
    
    'check cells A10:G26, if cells are empty, delete the row
        
    With ActiveSheet.Range("A10:G26")
    
        For Each iRange In .Rows
        
            If Application.CountA(iRange) = 0 Then
                
                iRange.EntireRow.Delete Shift:=xlUp
            
            End If
            
        Next

    End With

'add password protection
    ActiveSheet.Protect password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True


End Sub

 

[Anfinsen]

Seems like this would be super simple, but I'm not seeing my issue...
I have a spreadsheet that is locked. This is only bc the formulas have been mistakenly changed or incorrect by adding or deleting rows willy nilly.
I have created it with the max # of rows that should ever be needed, but typically they aren't all used.
After the spreadsheet is populated, I want VBA to remove the unused rows.
I will also do this for a second range (A35:E40).
Currently, only some of the rows are being deleted, instead of all blank rows in the range.
TIA!

This is the code:

Sub DeleteExtraRows()

' DeleteExtraRows - to remove any extra/blank rows (10-26) on worksheet

    Dim strPassword As String
    Dim iRange As Range
           
    strPassword = "password"
   
    'remove password protection
    ActiveSheet.Unprotect password:=strPassword
   
    'User warning
    MsgBox "This will delete the blank rows. Are you sure you want to delete?", vbOKCancel
   
    'check cells A10:G26, if cells are empty, delete the row
       
    With ActiveSheet.Range("A10:G26")
   
        For Each iRange In .Rows
       
            If Application.CountA(iRange) = 0 Then
               
                iRange.EntireRow.Delete Shift:=xlUp
           
            End If
           
        Next

    End With

'add password protection
    ActiveSheet.Protect password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True


End Sub

Hi Just,
It looks like your code will only remove rows where the cells in the range = 0. Replace that 0 with ""

It also looks like you declared iRange, and are referencing it in your delete line, but you never defined the variable for iRange.
You need to remove

With ActiveSheet.Range("A10:G26")

and the accompanying End With
Replace it with
iRange = Range("A10:G26")

I think this should do it