I have several really large excel data files and I need to go through them all and delete all rows where the value of the cell in column T is 1. Right now my code looks like:
Sub test()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("T5", "T900000")
If cell.Value = 1 Then
cell.EntireRow.Delete
End If
Next cell
End Sub
It seems to be working, but takes forever to run and I'm going to have to do this a bunch of times. Is there a better way of doing this, or some way to optimize what I already have to make it run faster?
This doesn't work as you think... When you delete rows as you iterate through them, you end up skipping rows. Example: imagine your rows have the numbers 1...10 in column A. You look at the first row and decide to delete it. Now you look at the second row. It has the number 3! You never looked at row 2!!
Better method would be to filter the spreadsheet on your criteria for column T, copy it, paste it I to a new worksheet (with formatting etc).
You can turn on macro recording and do this manually; then you will have the exact VBA code. I am sure that will be much faster.
Even if you don't do that, if you want to do a for each
where you delete things, reverse the order (start at the end and work backwards)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With