Excel VBA cell.EntireRow.Delete Not Deleting Every Row

[PMcE]

I have a procedure to sort and print a copy of the 'Summary' sheet of data - attendance records of a club.

As part of the procedure the data is first sorted on column L using the Named range PupilTotal.
This sorts the list in numerical order.
The procedure is then to delete rows where the value in col L is zero.

My problem is that it does not seem to delete every row where the value is zero.

In trying to resolve the problem I replaced the "cell.EntireRow.Delete" line with "cell.EntireRow.Font.Color = RGB(255, 0, 0)".
This does turn all rows wit zero in column L red.
So I do not understand why the delete line of code will not work for all the zero rows.

I am a relative newbie to VBA and my code has been put together by searching sites such as yours.
So any help or suggestions will be gratefully receive.


The code up to the delete row is as follows.

Sub EndOfTermSortAndPrint()

'This code is linked to the "End of Term Print" button on Roll sheet.
'Prep for printing
'Minimise window
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMinimized
'Display Message Box
If MsgBox("This Procedure will print the Summary sheet." & vbNewLine & "Ensure printer in loaded and switched on." & vbNewLine & " " & vbNewLine & "Do you wish to proceed?", vbYesNo) = vbYes Then
      GoTo 1 'i.e. get the code underway
   Else
   MsgBox "Routine Will Now End", vbCritical, "EXITING ROUINE"
    ActiveWindow.WindowState = xlMaximized
    Application.ScreenUpdating = True
    ActiveSheet.Range("A1").Select
      Exit Sub
   End If
1  'Code start here
'Select Summary sheet and make a copy
Sheets("Summary").Select
Sheets("Summary").Copy
    Dim cell As Range
    Dim cell1 As Range
    Dim cell2 As Range
    Dim cell3 As Range
    
    
  'Sort Pupil Total in Numerical order using range SortRange.
   
    Worksheets("Summary").Activate
    Range("SortRange").Select
    Selection.Sort Key1:=Range("L11"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    
    
    
'Check column L - Pupil Total (named range PupilTotal = Col L ).
'If value is zero delete the row .
    
    For Each cell In Range("PupilTotal")
        If Not IsEmpty(cell) Then
            If cell.Value = 0 Then
            cell.EntireRow.Delete
            End If
            End If
            Next

 

[MARK858]

If you are looping to delete rows then you have to loop from the bottom to the top because the delete shifts the rows up so use something like

Sub llopit()
    Dim c As Long, LR As Long
    LR = Range("L" & Rows.Count).End(xlUp).Row
    For c = LR To 2 Step -1
        If Cells(c, "L") = 0 Then Cells(c, "L").EntireRow.Delete
    Next
End Sub

If you want to use a named range you are better off using for instance autofilter (you are actually better off even with a normal range because it is faster than normal looping).
Something like...

Sub FilterIt()
    
    Worksheets("Summary").Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row).Name = "PupilTotal"
    
    With Range("PupilTotal")
        .AutoFilter Field:=1, Criteria1:="0"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Worksheets("Summary").AutoFilterMode = False
    End With
    
End Sub

Please note that you must have a header row when using autofilter

By the way you don't need to activate sheets or select ranges to work with them

 

[PMcE]

Hi Mark858,

Many thanks for your response.

I take it that the looping from the bottom instead of the top is a general rule for Excel VBA that should be used with any future routine?

With regards to the first solution i.e. not the autofilter code, I only wish the code to delete rows starting at L11 to the last pupil row. This unfortunately is not a static range. This is why I was using a Named Range.

As new pupils join the club during a term they are inserted therefore extending the range in comparison to last term, however not all pupils will be active in a particular term hence the zero amounts for some in column L.
Additionally there is information in the rows below the last pupil row that is required in the print out and therefore these rows cannot be deleted.

Is it possible for the Delete Row routine to first of all identify its range by starting at L11 and running down until there is a break - as there are some blank cells before the next rows appear. Once it has found the last pupil row to then work its way up to L11 deleting zero rows as it goes?

If you are looping to delete rows then you have to loop from the bottom to the top because the delete shifts the rows up so use something like

Sub llopit()
    Dim c As Long, LR As Long
    LR = Range("L" & Rows.Count).End(xlUp).Row
    For c = LR To 2 Step -1
        If Cells(c, "L") = 0 Then Cells(c, "L").EntireRow.Delete
    Next
End Sub

If you want to use a named range you are better off using for instance autofilter (you are actually better off even with a normal range because it is faster than normal looping).
Something like...

Sub FilterIt()
    
    Worksheets("Summary").Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row).Name = "PupilTotal"
    
    With Range("PupilTotal")
        .AutoFilter Field:=1, Criteria1:="0"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Worksheets("Summary").AutoFilterMode = False
    End With
    
End Sub

Please note that you must have a header row when using autofilter

By the way you don't need to activate sheets or select ranges to work with them

 

[MARK858]

Test the code below on a copy of your data.

Sub loopit()
    Dim c As Long, LR As Long

    LR = Worksheets("Summary").Range("L11").End(xlDown).Row

    For c = LR To 11 Step -1
        If Cells(c, "L") = 0 Then Cells(c, "L").EntireRow.Delete

    Next
End Sub

Edit: and just for completeness...
with the autofilter

Sub FilterIt()
    
    Worksheets("Summary").Range("L10:L" & Range("L11").End(xlDown).Row).Name = "PupilTotal"
    
    With Range("PupilTotal")
        .AutoFilter Field:=1, Criteria1:="0"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Worksheets("Summary").AutoFilterMode = False
    End With
    
End Sub

 

[farmerscott]

PMcE,

RE: looping.

you can loop from the top or the bottom. It is when you are deleting rows that you have to start from the bottom.

For example, if VBA comes to row 5 and it is deleted, row 6 moves up to row 5. As the code has already done row 5 then it moves onto row 6 which is the old row 7,completly missing the old row 6. On the otherhand if you are just clearing the contents of cells then this is not an issue as your original data is still in the same rows.

Have a look at some of the best coders on this site they tend to use looping rarely. They tend to use autofilter (as suggested by Mark858 above) or do something to the range in question, in one go. Looping can be very slow if you have a lot of rows.

hope that makes sense.

FarmerScott