Slow Macro

[jameshunt83]

Hi, I'm not very good with VBA really, but learning! Have written the below code, however its very slow! I'm sure there must be a better way of doing this

Sub basic_quote_rows()
Dim i As Long
Dim j As Long
i = 6
j = 19
Do While j < 95 And j > 19
If Sheets("material").Rows(i).Hidden = True Then Sheets("quotebasic").Rows(j).Hidden = True Else Sheets("quotebasic").Rows(j).Hidden = False
i = i + 1
j = j + 1
Loop

Dim x As Long
Dim y As Long
i = 92
j = 102
Do While j < 126 And j > 101
If Sheets("material").Rows(i).Hidden = True Then Sheets("quotebasic").Rows(j).Hidden = True Else Sheets("quotebasic").Rows(j).Hidden = False
i = i + 1
j = j + 1
Loop

End Sub

 

[schielrn]

See if this is any faster?

Sub basic_quote_rows()
Dim i As Long
Dim j As Long
application.screenupdating = false
application.calculation = xlcalculationmanual
i = 6
j = 19
Do While j < 95 And j > 19
If Sheets("material").Rows(i).Hidden = True Then Sheets("quotebasic").Rows(j).Hidden = True Else Sheets("quotebasic").Rows(j).Hidden = False
i = i + 1
j = j + 1
Loop

Dim x As Long
Dim y As Long
i = 92
j = 102
Do While j < 126 And j > 101
If Sheets("material").Rows(i).Hidden = True Then Sheets("quotebasic").Rows(j).Hidden = True Else Sheets("quotebasic").Rows(j).Hidden = False
i = i + 1
j = j + 1
Loop

application.calculation = xlcalculationautomatic
application.screenupdating = true
End Sub

Hope that helps.

 

[jameshunt83]

Yeah thats a bit better. So did I write the rest of it correctly then?

 

[schielrn]

I mean it looks like a basic loop and don't really see much else to speed it up. I like to use for statements rather than do statements, but everyone has their own preferences.

Sub basic_quote_rows()
Dim i As Long
Dim j As Long
application.screenupdating = false
application.calculation = xlcalculationmanual

for j = 20 to 95
i = j - 13
If Sheets("material").Rows(i).Hidden = True Then Sheets("quotebasic").Rows(j).Hidden = True Else Sheets("quotebasic").Rows(j).Hidden = False
next j

for j = 102 to 125
i = j-10
If Sheets("material").Rows(i).Hidden = True Then Sheets("quotebasic").Rows(j).Hidden = True Else Sheets("quotebasic").Rows(j).Hidden = False
next j

application.calculation = xlcalculationautomatic
application.screenupdating = true
End Sub