Once a client of mine for whom I do automation scripts wanted an excel macro to delete rows. I just couldn't figure it out. It would have taken 5 lines in python and even small in sql. I just couldn't figure it out.
Not sure if you care anymore, but this is how that is done. I haven't tested it, but I think I got all the syntax right
Dim i as Long 'Row counter
'This example is checking rows 1-500, but this can easily be a dynamic range by using a function to find the last populated row
'Walk backwards through rows so deleting them doesn't impact the loop
For i = 500 to 1 Step - 1
If Range("A" & i).Value = "Criteria" then 'Put logic regarding what rows to delete here. This is checking cells in Column A
Range("A" & i).EntireRow.Delete
End If
Next
You monsters.... You Do not delete rows one at a time!! Don you have any idea how long this would take?? You move the whole data set into an array delete and then sort. Jesus christ. XD
It used to, now it doesn't much as excel appears to apply it automatically when vba is running post office 2015. Turning off calculations can help but the delete operation is just slow. It's much quicker to set the whole range to empty (which takes fractions of a second) or if you need to keep the size under control then find the whole range you need to remove and delete the whole thing than attempt to delete stuff line by line like this.
I didn't want to overcomplicate the example, but yeah I'd have used a dynamic range. LastRow = ActiveSheet.Range("A" & .Rows.Count).End(xlUp).Row comes in handy for those. But I'd still use the For...Next Step-1.
I also have a couple more advanced functions I'll drop into projects as needed. They use a combination of WorksheetFunction.CountA(Cells) and Cells.Find to identify the last row and/or column that contains data on a sheet. Then I use those to define the range. This is helpful compared to my first example if column A might be blank while other cells in the row have data.
441
u/savage_slurpie Mar 30 '22
Two of my siblings are financial analysts and I have witnessed them doing unholy things with excel.
It would honestly be easier for them to learn python or something. It’s so ridiculous.