Just for the record, I have added "Option Explicit" and the compiler found 3
variables I had not defined...I fixed that...I commented out all the
unnecessary select statements, and revamped my delete, copy and paste type
statements.

I timed the performance with a stop watch before and after making these
changes, with the same "other applications" open on my computer and the
performance did improve a little....went from 9 minutes 40 seconds down to 9
minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I
was hoping for more, so now I am going to study hard on the use of
calculation statements in the routine. I have it turned to manual at the
beginning...I need to make sure I am only forcing the recalculation when
absolutely necessary.

"Dave Peterson" wrote:

I think it's very useful for short code, too.

As well as making typos easier to find, if you declare your variables correctly:

Dim wks as worksheet
not
dim wks as variant 'or as object

Then you'll get VBAs helpful intellisense--that's the popup that you see after
typing the dot in:

wks.

You'll see a list of all the properties and methods that you could use.

JLGWhiz wrote:

Option Explicit means that all of your variables have to be declared. If
they are not, the first one you try to use that is not declared will generate
an error. It also helps you to catch typos because VBA will see the typo as
a new undeclared variable and send you a message. For short procedures it is
not that important, but it can save you a lot of grief in long and complex
procedures.

"Eric @ BP-EVV" wrote:

Based on your reply, Dave, along with those of Paul and Don, it's
unanimous....I'm an amateur at this VBA coding stuff ! :-) None the less,
this forum has always been of great help to me and I am grateful for the
responses I get to my questions. I will implement the suggestions from y'all
and see how my routine's performance changes.

One more question: what does the "Option Explicit" do / mean ?

Thanks again !


"Dave Peterson" wrote:

#1. Nope.

dim RngToCopy as range
dim DestCell as range

set rngtocopy = worksheets("Sheet999").range("x1:z99")
set destcell = worksheets("sheet888").range("u71")

rngtocopy.copy _
destination:=destcell

#2. Nope.

worksheets("sheet999").range("z99").value = "what you want here"

#3. Nope.

worksheets("sheet999").rows(17).delete


==========
I'd turn calculation to manual, then do the work, then change the calculation
back to whatever it was before.

In fact, there are a few things that can slow down macros. I do this kind of
thing to speed them up:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Your code will replace the "'do the work" line.

When/if you're hiding rows/columns, excel wants to figure out where to draw
those lines each time you change the layout.


Eric @ BP-EVV wrote:

1. Do you have to "select" a worksheet or cell (or range of cells) before you
can copy a cell (or range of cells) from that worksheet ?
2. Do you have to "select" a worksheet or cell before you can assign a
formula to a cell in that worksheet ?
3. Do you have to "select" a row in a worksheet before you delete that row ?

These may seem like very basic VBA questions....and in reality they
are....what I am trying to get at is efficiency. I have some code in a
workbook that pulls data from a SQL table on an AS/400 system and them
performs several calcuations, sorts, etc. on that data. When I run this
routine for all 17 locations at one time, it takes nearly 10 minutes to run
to completion. What I'm really after here is suggestions on how to make my
code more efficient....what can I do to speed up this process ?

Thanks !

--

Dave Peterson


--

Dave Peterson