VBA procedure too large

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
Hi. I get the error that my procedure is too large. What I'm doing is copy pasting values from N to O only in even rows starting from 6. Please how can I reduce the procedure? Thanks for any help!

Code:
Selection.Copy
    Range("O6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
What I'm doing is copy pasting values from N to O only in even rows starting from 6.

Hi - does this work for for?

Code:
Sub m()
Dim i As Long
For i = 6 To Range("N" & Rows.Count).End(xlUp).Row Step 2
    Range("N" & i).Resize(, 2).Value = Range("N" & i).Resize(, 2).Value
Next i
End Sub
 
Upvote 0
Thanks for the quick answer! No it doesn't do anything. I'm a newbie to VBA so sorry for the stupid question but isn't row O missing?
 
Upvote 0
The resize() extends the range to column O.

What does the msgbox return in this version?

Code:
Sub m()
Dim i As Long
MsgBox Range("N" & Rows.Count).End(xlUp).Row
For i = 6 To Range("N" & Rows.Count).End(xlUp).Row Step 2
    Range("N" & i).Resize(, 2).Value = Range("N" & i).Resize(, 2).Value
Next i
End Sub
 
Upvote 0
And nothing happens at all - i.e. the formulas in the even rows stay as formulas?
 
Upvote 0
I think this:
Code:
    Range("N" & i).Resize(, 2).Value = Range("N" & i).Resize(, 2).Value
should be this:
Code:
    Range("N" & i).Resize(, 2).Value = Range("N" & i).Value
or, if N should remain as it is (it may contain formulas):
Code:
    Range("O" & i).Value = Range("N" & i).Value
 
Upvote 0
Thanks guys it now works but this was actually not what I was looking for. Because I have a circular reference error (to put it simply column N is derived from column O) I recorded the macro. I will try to explain it but please bear with me because my native language is not english. The problem with the VBA is that it needs to copy paste first N6 to O6 and then the values in column N after N6 changes. So it needs to copy N6 to O6 then let it recalculate the values of N (N has a formula), and then copy N8 to O8 etc.
When I recorded the macro up to row 51 (data ends there), the solution was what I wanted. The problem here that I will add lots and lots of rows after row 51 (around 850). So after I recorded the macro up to row 51 I entered the VBA, copy pasted 850 times the code line and changed the cells up to N900 (with replace).

Then when I run the macro, it told me that the procedure is too large. So I cut 300 lines and then it was able to run. Unfortunately, when it run the result was the same with your simple code -it copy pasted all the values at once.

Please help me I'm desperate! I'm trying to fix the problem for several days now!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top