We can avoid using loop in this case, try to replace your looping code
with offset and resize, try this another solution
Dim u As Integer
u = Range("Months").Value - 1
If u > 1 Then
Range("ColumnLong").Resize(, u).Insert
Range("ColumnFormula").Copy
Range("ColumnLong").Offset(, -u).Resize(, u).PasteSpecial
Paste:=xlFormulas
Application.CutCopyMode = False
End If
Rgds,
[dp]
On 14/09/2015 6:31, Isabel Cristina Ortiz wrote:
Hello Ashish,
Thank you for your response.
This is a simplified copy of my macro. The lines you can find there
arerepeated multiple times in my final workbook, in different
spreadsheets because it has a broader mathematical formulation.
Hope this helps,
Regards
On Saturday, September 12, 2015 at 2:19:22 PM UTC-5, ashish wrote:
Can u pls share a sample file
On Sep 12, 2015 9:38 PM, "Isabel Cristina Ortiz" <ior...@gmail.com
<javascript:>> wrote:
Thank you Basole for your message!
I have been already using your recommendation on my code, but
it still takes a lot of memory.
Regards,
Isabel
On Saturday, September 12, 2015 at 9:20:05 AM UTC-5, Basole
wrote:
Hi isabel, try using "Application.ScreenUpdating = False"
To Turn Off at the start of code. and
"Application.Calculation = xlCalculationManual" To turn
off the automatic calculation
at the beginning of the code
see example:
Dim uAs Integer, vAs Integer
u = Range("Months").Value - 1
Application.ScreenUpdating =False 'To Turn Off at the start of
code.
Application.Calculation = xlCalculationManual'To turn off the
automatic calculation
If u > 1Then
For v = 1To u - 1
Application.Goto Range("ColumnLong"),True
Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Range("ColumnFormulas"),True
Selection.Copy
Selection.Offset(0, -2).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:=_
False, Transpose:=False
Application.CutCopyMode =False
Next v
Else
End If
Application.ScreenUpdating =True 'To Turn on at the end of the
code.
Application.Calculation = xlCalculationAutomatic'To turn On the
automatic calculation
Regards.
2015-09-11 13:31 GMT-03:00 Isabel Cristina Ortiz
<ior...@gmail.com>:
Hello,
I have been working on a workbook. It is a cash flow
and macro helps me to change the numbers of years
(columns) because each project needs different time
periods.
What I do is that I have two columns named as
"ColumnLong" and "ColumnFormulas". The first one is
empty and only serves as a reference point to add more
cells to the left to it, according to the number of
months of the cash flow.
Then, I always check the number of months on a
specific cell named "Months" and I have a loop, it
insert a column left to "ColumnLong", then goes to
"ColumnFormulas" and it paste it into the new column
which is two columns left to "ColumnFormulas".
What I need is a simpler code because I have several
loops similar to this one and I am having memory problems.
I have been working on some recommendations I found on
http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html
<http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html>
but wasn't able to apply to this code.
I hope you can help me.
Best regards,
Isabel
This is the code I am using:
Dim u As Integer, v As Integer
u = Range("Months").Value - 1
If u > 1 Then
For v = 1 To u - 1
Application.Goto Range("ColumnLong"), True
Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Range("ColumnFormulas"), True
Selection.Copy
Selection.Offset(0, - 2).Select
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next v
Else
End If
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do
you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official
Facebook page of this forum @
https://www.facebook.com/discussexcel
<https://www.facebook.com/discussexcel>
FORUM RULES
1) Use concise, accurate thread titles. Poor thread
titles, like Please Help, Urgent, Need Help, Formula
Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or
bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not
allowed.
NOTE : Don't ever post confidential data in a
workbook. Forum owners and members are not responsible
for any loss.
---
You received this message because you are subscribed
to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving
emails from it, send an email to
excel-macros...@googlegroups.com.
To post to this group, send email to
excel-...@googlegroups.com.
Visit this group at
http://groups.google.com/group/excel-macros
<http://groups.google.com/group/excel-macros>.
For more options, visit
https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you
wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook
page of this forum @ https://www.facebook.com/discussexcel
<https://www.facebook.com/discussexcel>
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles,
like Please Help, Urgent, Need Help, Formula Problem, Code
Problem, and Need Advice will not get quick attention or may
not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any
security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to excel-macros...@googlegroups.com
<javascript:>.
To post to this group, send email to
excel-...@googlegroups.com <javascript:>.
Visit this group at
http://groups.google.com/group/excel-macros
<http://groups.google.com/group/excel-macros>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners
and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros+unsubscr...@googlegroups.com>.
To post to this group, send email to excel-macros@googlegroups.com
<mailto:excel-macros@googlegroups.com>.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
