Macro Going Slow

[Jared_Jones_23]

Hello I have a macro that opens another workbook and deletes rows based on specified criteria. The code that deletes the rows works fine but runs extremely slow. When I step through it my self it seems to work relatively quick but it gets caught up when its running. Here is the sub program that does the deleting. Any suggestions are appreciated thank you.
Jared

Sub delbrows2(fr, cse, delcnt)
Dim r As Integer
Application.ScreenUpdating = False
For r = Cells(rows.Count, 13).End(xlUp).End(xlUp).Row To fr Step -1 'For r = 1000 To 1 Step -1
If Cells(r, 13) <> cse Then
rows(r).Delete
'delcnt = delcnt + 1
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[VoG]

Perhaps

Sub delbrows2(fr, cse, delcnt)
Dim r As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For r = Cells(Rows.Count, 13).End(xlUp).End(xlUp).Row To fr Step -1 'For r = 1000 To 1 Step -1
    If Cells(r, 13) <> cse Then
        Rows(r).Delete
        'delcnt = delcnt + 1
    End If
Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[kpark91]

Hi, when excel deletes rows, it makes tons of operations in order to delete one row.
(deleting row, moving up the cells below with all the formatting and so on...)

So, the best way to approach this is to first create a large cell area you can delete off the bat. So, here is the algorithm you can work with:
1) make a column
2) in that column, have it return TRUE or FALSE based on your criteria
3) filter/sort it by TRUE/FALSE column
4) Select the area you want to delete in one chunk
5) Delete

 

[Jared_Jones_23]

I thought about trying to store the rows that should be deleted in an array and then deleting all the rows at once based on the numbers of each row in the array. I didnt know how to go about this though does anyone else have an idea? Thank you

 

[kpark91]

Hi, Jared. storing the row numbers in an array and deleting them would be equivalent as deleting the rows one by one.

So, I believe you have no choice to go with sorting/filtering. (as far as I know..)

 

[Jared_Jones_23]

Alright because it would still have to go through the array and individualy delete them? When you say create a column with true false and then delete a large block at once are you saying to do that in a macro?

 

[kpark91]

you could do it in the macro when you could do it on excel spreadsheet.

So, yes, create a column with true/false
sort it in that true/false column then you would end up with large chunk of data with F and T with no alternating values inbetween.
So, you only need to find the last cell of one value (whether it be F or T), and select then delete the rows.

So, if your data looks like this and you want to delete values less than or equal to 5

1
5
8
7
4

You create a colum nwith T/F

1    T
5    T
8    F
7    F
4    T

Sort

1    T
5    T
4    T
8    F
7    F

Delete T

8    F
7    F

Delete column

8
7

 

[Jared_Jones_23]

How do you sort the trues and falses in the macro. Also can you have the macro filter the data by a column and then just copy the filtered rows rather than deleting the ones I dont need and copying?

 

[kpark91]

You could do that if you want.

Always record your macro if you have an idea but don't know where to start.
Then clean up the macro.

Macro recorder is your friend!

It is up to you to decide how the data should be dealt with.

 

[Jared_Jones_23]

I have it working where it filters the data on the opened worksheet but when i copy it it pastes everything not just the rows that have been filtered. Is there a special copy or something to leave out hidden rows? Thank you i appreciate all the help