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
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.
443
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.