Thanks for that information....it sounds like I need to be using "Option
Explicit" for future coding projects.

"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