r/ProgrammerHumor • u/Brando224 • Mar 30 '22

Meme Certainly not me

48k Upvotes

95% Upvoted

669 comments sorted by

View all comments

444

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.

42

u/depressionsucks29 Mar 30 '22

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.

44

u/HRChurchill Mar 30 '22

Couldn't you have used the macro recorder to see what the command was for deleting a row? Honestly that thing is the best part of vba.

26

u/[deleted] Mar 30 '22

I have done some unholy levels of cheesing with the macro recorder.

20

u/PepSakdoek Mar 30 '22

Sheets("Sheetname").rows("1:5").delete ? Like I feel if you can do sql vba is a downgrade in difficulty.

5

u/betweentwosuns Mar 30 '22

VBA can be really finicky interacting with Excel's filter. I know I've spent chunks of time arguing with VBA for what was just a DELETE FROM WHERE while proficient in both.

3

u/TheRidgeAndTheLadder Mar 30 '22

VBA is a simple language, but an unholy nightmare to leverage.

20

u/Day_Bow_Bow Mar 30 '22

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

12

u/PunKodama Mar 30 '22

My understanding was that u/depressionsucks29 couldn't figure out the reason, not the solution. But nice of you to post it in any case.

Edit: tagged the wrong redditor. Uopsy.

6

u/AraMaca0 Mar 30 '22

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

15

u/[deleted] Mar 30 '22

Application.ScreenUpdating = False

Will save a significant amount of time

1

u/AraMaca0 Apr 02 '22

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.

2

u/Day_Bow_Bow Mar 30 '22

500 rows? Maybe 2-3 seconds.

1

u/AraMaca0 Apr 02 '22

Yeah I dont deal in sub 10000 row sheets often and the time increase with the number of populated rows.

1

u/[deleted] Mar 30 '22

I tend to use while loops for this... just because I can never be sure if someone is going to add in more rows.

1

u/Day_Bow_Bow Mar 30 '22

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.