Quick VBA

Duffnut

Board Regular
Joined
Jan 20, 2003
Messages
65
HI Guys,

I've got this line in my code to put the message at the bottom. But what line do I need to put in to get it back to normal when it has finished.

Application.StatusBar = "Please Wait - Multiple Calculations Taking Place (Approx. 5-6 Mins)"

Cheers
Andy
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi, Andy,

Code:
Application.StatusBar = False
if you share a bit of your "loops", you can add some functionality
instead of telling users "wait 5-6 minutes", you can show them how much of the task is done (%)
you could even show an estimation at what time the procedure will be finished

kind regards,
Erik
 
Upvote 0
Cheers both. Try the obvious ext time duffnut. D'oh.

That sounds good Erik. How I do that? This is what I have ( bit messy but it works at the moment ).

Sheets("QUERY").Visible = True
Application.StatusBar = "Please Wait - Multiple Calculations Taking Place (Approx. 5-6 Mins)"
Sheets("Temp final Data").Visible = True
Sheets("Sheet1").Visible = True
Application.ScreenUpdating = False
Sheets("QUERY").Select
Columns("A:M").Select
Selection.Copy
Sheets("Temp final Data").Select
Cells.Select
Range("A1037").Activate
ActiveWindow.ScrollRow = 1
Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:M").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Find(What:="#value", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Sheets("QUERY").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Temp final Data").Select
ActiveWindow.SelectedSheets.Visible = False
Application.StatusBar = False
Selection.End(xlUp).Select
ActiveWindow.SelectedSheets.Visible = False
 
Upvote 0
TIP: use CODEbutton to display code

There is no loop...
When there is no loop you can add more messages before each action.

Thinking aloud...
IMPORTANT REMARK:
don't copy entire columns or sheets
restrict the range to what is needed
VERY IMPORTANT
your workbook is rather slow in calculationspeed, I presume
1. couldn't you cleanup to have it calculating faster ?
2. do you really need to calculate during the process ?

...hmmm, now I see there is not a lot
this is the "action-part"
Code:
Sheets("QUERY").Select 
Columns("A:M").Select 
Selection.Copy 
Sheets("Temp final Data").Select 
Cells.Select 
Range("A1037").Activate 
ActiveWindow.ScrollRow = 1 
Range("A1").Select 
ActiveSheet.Paste 
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
False, Transpose:=False 
Columns("A:M").Select 
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
Rows("2:2").Select 
Selection.Delete Shift:=xlUp 
Columns("A:A").Select 
Selection.Find(What:="#value", After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False).Activate

seems like you can enhance to get the following code

Code:
Sheets("QUERY").Select
Columns("A:M").Copy

Sheets("Temp final Data").Select
'don't think next lines are neede
'Range("A1").Select
'ActiveSheet.Paste 
Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:M").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("2:2").Delete Shift:=xlUp

'next line is dangerous: if the item is not found, the code will bug
'so only use if you're sure "#value" will appear else error-handling needed
Columns("A:A").Find(What:="#value", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
before this process you can add
Code:
Application.Calculation = xlCalculationManual
after
Code:
Application.Calculation = xlCalculationAutomatic

so you'll get
Code:
Application.Calculation = xlCalculationManual
Sheets("QUERY").Select
Columns("A:M").Copy

Sheets("Temp final Data").Select
Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:M").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("2:2").Delete Shift:=xlUp

'next line isdangerous: if the item is not found, the code will bug
'so only use if you're sure "#value" will appear else error-handling needed
Columns("A:A").Find(What:="#value", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

Application.Calculation = xlCalculationAutomatic
this should speed up a lot (reduce calculation time to 1/4)

best regards,
Erik
 
Upvote 0
You're WELCOME :)

suggestion: start another thread trying to get some tips about your calculationspeed: post an overview, some formulas ...

check out "search" and recommended add-ins and link before ...

greetings from Belgium,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top