Issues deleting un-needed rows

Dalton325

New Member
Joined
Mar 18, 2015
Messages
10
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,460
Office Version
  1. 365
Platform
  1. Windows
When deleting rows like that you should loop from the bottom up like
VBA Code:
For j = LastRow to 2 Step -1
and delete this line
VBA Code:
j = j - 1
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,434
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Mar 18, 2015
Messages
10
When deleting rows like that you should loop from the bottom up like
VBA Code:
For j = LastRow to 2 Step -1
and delete this line
VBA Code:
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

New Member
Joined
Mar 18, 2015
Messages
10
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.
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,460
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top