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 !

other more knowledgeable will probably respond, but to answer all 3 questions,
no.

in fact, it's preferable not to,.

make sure you turn off screen updating and set calculation to manual while your
code executes and then set calculation back to automatic when it's finished.

--


Gary


"Eric @ BP-EVV" <(E-Mail Removed)> wrote in message
news:7EE2A731-BDA4-45EF-836D-(E-Mail Removed)...
> 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 !
>

Hi
No to all three.
If tou use the macro recorder you typically get

Something.Select
Selection.DoSomething

You can always cut out the middle bit

Something.DoSomething

Have a look at your code and see how many of these selections you can
remove.
regards
Paul


On Sep 16, 3:21*pm, Eric @ BP-EVV
<EricBP...@discussions.microsoft.com> wrote:
> 1. Do you have to "select" a worksheet or cell (or range of cells) beforeyou
> 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 !

Gary - thanks for the reply.... I have already got screen updating turned
off, as well as having set calculation to manual. I'm sure I can elminate
some code by not selecting cells first, which I would think can only help the
speed of this routine.

Does anyone else have other suggestions for improving speed or efficiency ?

Thanks !

"Gary Keramidas" wrote:

> other more knowledgeable will probably respond, but to answer all 3 questions,
> no.
>
> in fact, it's preferable not to,.
>
> make sure you turn off screen updating and set calculation to manual while your
> code executes and then set calculation back to automatic when it's finished.
>
> --
>
>
> Gary
>
>
> "Eric @ BP-EVV" <(E-Mail Removed)> wrote in message
> news:7EE2A731-BDA4-45EF-836D-(E-Mail Removed)...
> > 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 !
> >
>
>
>

No to all

range("a2:a22").copy sheets("destshtname").range("a1")

sheets("destshtname").range("a1").formula="a1*a2"

sheets("destshtname").rows(3).delete

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Eric @ BP-EVV" <(E-Mail Removed)> wrote in message
news:7EE2A731-BDA4-45EF-836D-(E-Mail Removed)...
> 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 !
>

#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

#2. Nope.

worksheets("sheet999").range("z99").formula = "=sum(a:a)"

(using a formula)

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

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
>

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
> >

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
> > >