Issues deleting un-needed rows

[Dalton325]

Hey,

Hoping someone can help me.

I've been trying different stuff all morning and kind of have my code limping along.

What I'm trying to do is export an excel spreadsheet from another program. It fills it with junk, but a material description is always in column A and the qty is always in the same row of column B.

My idea is to find the last row, then loop through from A1 down to ALastRow. Check the first two letters of A? and if there don't match the (5) I'm looking for, then delete the row. Ideally, I would end up with a spread sheet, where each row has just the relevant info. Then I'll delete columns B and D-whatever. Sort by name and then add up the qty. That way, I can export this document with all the material we order from a vendor over a period of time, run this program, and end up with a spread sheet that shows each unique material once, and has the total qty of that material.

The below code now seems to work, but it goes "infinate loop" on me. I've even tried to put in breaks at 100,500,1000,1500,2000, and 2500 rows. I had code to pop up a message box and show me it had reached that point. It gets to the 100 msgbox, but not the 500. There were approximately 3300 rows in this sheet. I let it run for a while, but it never finished. The part that displayed did appear to have worked correctly.

Sub MaterialQty()
    
    'find last row in worksheet
    Call FindLastRow(LastRow)
    
    test = ""
'    MsgBox (LastRow)
    
    'loop, in column A, from first cell to last cell.
    For j = 1 To LastRow
    
        'set "test" to cell value
        test = Range("A" & j)

        'check if cell value starts with any of these beginnings
       If Left(test, 2) = "CR" _
            Or Left(test, 2) = "HR" _
            Or Left(test, 2) = "AL" _
            Or Left(test, 2) = "GA" _
            Or Left(test, 2) = "SS" Then
        
            'Do nothing and skip to end if
        Else
           
           'if it's not equal, delete the row
            Sheet1.Cells(j, 1).EntireRow.Delete
            
            'deleting row bumps up all rows, subtract "1" to check that row again
            j = j - 1


        End If
    Next
        
End Sub

 

[Fluff]

When deleting rows like that you should loop from the bottom up like

For j = LastRow to 2 Step -1

and delete this line

j = j - 1

 

[jasonb75]

Because of the way you've done it, you're entering an infinite loop by hitting an empty row after the last valid row to keep. Starting from the bottom and working backwards is more robust and more efficient.

There may be room for further improvements to efficiency, I've edited as I've suggested above but kept the rest the same so that it is easier for you to follow. I haven't tested it but the rest looks as though it should run without issue.

Sub MaterialQty()
    
    'find last row in worksheet
    Call FindLastRow(LastRow)
    
    test = ""
'    MsgBox (LastRow)
    
    'loop, in column A, from first cell to last cell.
    For j = LastRow To 1 Step -1
    
        'set "test" to cell value
        test = Range("A" & j)

        'check if cell value starts with any of these beginnings
       If Left(test, 2) = "CR" _
            Or Left(test, 2) = "HR" _
            Or Left(test, 2) = "AL" _
            Or Left(test, 2) = "GA" _
            Or Left(test, 2) = "SS" Then
        
            'Do nothing and skip to end if
        Else
          
           'if it's not equal, delete the row
            Sheet1.Cells(j, 1).EntireRow.Delete
            
 


        End If
    Next
        
End Sub

 

[Dalton325]

When deleting rows like that you should loop from the bottom up like

For j = LastRow to 2 Step -1

and delete this line

j = j - 1

Sweet! That worked. It's been a while since I've worked with VBA and I was never a super coder. Thanks.

 

[Dalton325]

Because of the way you've done it, you're entering an infinite loop by hitting an empty row after the last valid row to keep. Starting from the bottom and working backwards is more robust and more efficient.

There may be room for further improvements to efficiency, I've edited as I've suggested above but kept the rest the same so that it is easier for you to follow. I haven't tested it but the rest looks as though it should run without issue.

Sub MaterialQty()
   
    'find last row in worksheet
    Call FindLastRow(LastRow)
   
    test = ""
'    MsgBox (LastRow)
   
    'loop, in column A, from first cell to last cell.
    For j = LastRow To 1 Step -1
   
        'set "test" to cell value
        test = Range("A" & j)

        'check if cell value starts with any of these beginnings
       If Left(test, 2) = "CR" _
            Or Left(test, 2) = "HR" _
            Or Left(test, 2) = "AL" _
            Or Left(test, 2) = "GA" _
            Or Left(test, 2) = "SS" Then
       
            'Do nothing and skip to end if
        Else
         
           'if it's not equal, delete the row
            Sheet1.Cells(j, 1).EntireRow.Delete
           



        End If
    Next
       
End Sub

Thanks for the help. Starting from the bottom worked. No more loop. Thanks for explaining why.

 

[Fluff]

Glad we could help & thanks for the feedback.