Can this simple VBA code be speeded up?

[Dobo_Bobo]

Please can someone help me improve my VBA code below?

I'm teaching myself VBA and I've written a macro that works but I want to learn how to improve the code so the macro runs quicker. One part of the code that is slow is a creation of a temporary list that needs to be created on a temporary sheet, once the macro is completed the sheet has to deleted, so I can't store the list anywhere.


When the macro runs, the strings are entered one at a time and it takes a while to complete the long list. I've searched the web and I can't find anything to speed up this part, the quickest way I've found is using 'Step Into' on the VBA editor by quickly pressing F8. Pressing F8 really quickly makes this part of the macro run really fast but of course this isn't a viable option.


Below is an extract (S1 is the list's heading):


'In column S this list is created
Range("S1") = "Task Type"
Range("S2") = "Approval"
Range("S3") = "Approval"
Range("S4") = "Approval"
Range("S5") = "Approval"
Range("S6") = "Approval"
Range("S7") = "Approval"
Range("S8") = "Approval"
Range("S9") = "Approval"
Range("S10") = "Approval"
Range("S11") = "Task"
Range("S12") = "Task"
Range("S13") = "Task"
Range("S14") = "Task"
Range("S15") = "Task"
Range("S16") = "Task"
Range("S17") = "Task"
Range("S18") = "Task"
Range("S19") = "Task"


If anyone can teach me a quicker way I will really appreciate it and I will return to give thanks!

 

[dreid1011]

You could combine each same string like this:

Range("S1") = "Task Type"
Range("S2:S10") = "Approval"
Range("S11:S19") = "Task"

 

[Dobo_Bobo]

You could combine each same string like this:

Range("S1") = "Task Type" Range("S2:S10") = "Approval" Range("S11:S19") = "Task"

D'oh! I feel dumb for no thinking of that, I was trying all sorts I found on the web!

Many thanks for your help dreid1011!!! I've tested it out and it works a treat. I've also tried it with the code below that I've just discovered from the web and it speeds it up just that little bit more. But your code was exactly what I needed.

Thank you!

Sub InsertText()

  
 Application.Calculation = xlCalculationManual
 Application.ScreenUpdating = False
 Application.DisplayStatusBar = False
 Application.EnableEvents = False
  
  
 Range("S1") = "Task Type"
 Range("S2:S10") = "Approval"
 Range("S11:S19") = "Task"
  
  
 Application.EnableEvents = True
 Application.DisplayStatusBar = True
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
  
  

 End Sub

 

[dreid1011]

Ah yes, there is that too. You're quite welcome.