Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reducing Size Of Excel Recorded Macro Through VB! 1

Status
Not open for further replies.
APElliott

APElliott

Technical User
Jul 9, 2002
165
GB
Hello,

I,ve recoreded the following macro in excel, but it is absolutely huge. It is that big i have had to tie 4 macros together using 1 macro that just runs these 4 one after another.

Here is an abstract from the macro - could someone tell me if there is a way of shortening this code and how:

Sheets("Material").Select
Range("j1").Select
Selection.Copy
Range("i1").Select
Selection.PasteSpecial Paste:=xlValues
Range("c2:c99").Select
Selection.Copy
Range("h2").Select
Selection.PasteSpecial Paste:=xlValues
Range("c2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i2").Select
Selection.Copy
Range("j2").Select
Selection.PasteSpecial Paste:=xlValues
Range("h2").Select
Application.CutCopyMode = False
Selection.Copy
Range("c2").Select
Selection.PasteSpecial Paste:=xlValues
Range("c3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i3").Select
Selection.Copy
Range("j3").Select
Selection.PasteSpecial Paste:=xlValues
Range("h3").Select
Application.CutCopyMode = False
Selection.Copy
Range("c3").Select
Selection.PasteSpecial Paste:=xlValues
Range("c4").Select
ActiveCell.FormulaR1C1 = "0"
Range("i4").Select
Selection.Copy
Range("j4").Select
Selection.PasteSpecial Paste:=xlValues
Range("h4").Select
Application.CutCopyMode = False
Selection.Copy
Range("c4").Select
Selection.PasteSpecial Paste:=xlValues
Range("c5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i5").Select
Selection.Copy
Range("j5").Select
Selection.PasteSpecial Paste:=xlValues
Range("h5").Select
Application.CutCopyMode = False
Selection.Copy
Range("c5").Select
Selection.PasteSpecial Paste:=xlValues
Range("c6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i6").Select
Selection.Copy
Range("j6").Select
Selection.PasteSpecial Paste:=xlValues
Range("h6").Select
Application.CutCopyMode = False
Selection.Copy
Range("c6").Select
Selection.PasteSpecial Paste:=xlValues
Range("c7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i7").Select
Selection.Copy
Range("j7").Select
Selection.PasteSpecial Paste:=xlValues
Range("h7").Select
Application.CutCopyMode = False
Selection.Copy
Range("c7").Select
Selection.PasteSpecial Paste:=xlValues
Range("c8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i8").Select
Selection.Copy
Range("j8").Select
Selection.PasteSpecial Paste:=xlValues
Range("h8").Select
Application.CutCopyMode = False
Selection.Copy
Range("c8").Select
Selection.PasteSpecial Paste:=xlValues
Range("c9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i9").Select
Selection.Copy
Range("j9").Select
Selection.PasteSpecial Paste:=xlValues
Range("h9").Select
Application.CutCopyMode = False
Selection.Copy
Range("c9").Select
Selection.PasteSpecial Paste:=xlValues
Range("c10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i10").Select
Selection.Copy
Range("j10").Select
Selection.PasteSpecial Paste:=xlValues
Range("h10").Select
Application.CutCopyMode = False
Selection.Copy
Range("c10").Select
Selection.PasteSpecial Paste:=xlValues
Range("c11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i11").Select
Selection.Copy
Range("j11").Select
Selection.PasteSpecial Paste:=xlValues
Range("h11").Select
Application.CutCopyMode = False
Selection.Copy
Range("c11").Select
Selection.PasteSpecial Paste:=xlValues
Range("c12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i12").Select
Selection.Copy
Range("j12").Select
Selection.PasteSpecial Paste:=xlValues
Range("h12").Select
Application.CutCopyMode = False
Selection.Copy
Range("c12").Select
Selection.PasteSpecial Paste:=xlValues
Range("c13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i13").Select
Selection.Copy
Range("j13").Select
Selection.PasteSpecial Paste:=xlValues
Range("h13").Select
Application.CutCopyMode = False
Selection.Copy
Range("c13").Select
Selection.PasteSpecial Paste:=xlValues
Range("c14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i14").Select
Selection.Copy
Range("j14").Select
Selection.PasteSpecial Paste:=xlValues
Range("h14").Select
Application.CutCopyMode = False
Selection.Copy
Range("c14").Select
Selection.PasteSpecial Paste:=xlValues
Range("c15").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i15").Select
Selection.Copy
Range("j15").Select
Selection.PasteSpecial Paste:=xlValues
Range("h15").Select
Application.CutCopyMode = False
Selection.Copy
Range("c15").Select
Selection.PasteSpecial Paste:=xlValues
Range("c16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i16").Select
Selection.Copy
Range("j16").Select
Selection.PasteSpecial Paste:=xlValues
Range("h16").Select
Application.CutCopyMode = False
Selection.Copy
Range("c16").Select
Selection.PasteSpecial Paste:=xlValues
Range("c17").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i17").Select
Selection.Copy
Range("j17").Select
Selection.PasteSpecial Paste:=xlValues
Range("h17").Select
Application.CutCopyMode = False
Selection.Copy
Range("c17").Select
Selection.PasteSpecial Paste:=xlValues
Range("c18").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("i18").Select
Selection.Copy
Range("j18").Select
Selection.PasteSpecial Paste:=xlValues
Range("h18").Select
Application.CutCopyMode = False
Selection.Copy
Range("c18").Select
Selection.PasteSpecial Paste:=xlValues

As you can see this abstract is fairly long, but it goes on to row 600.

Thankyou,

Andrew
 
Sort by date Sort by votes
You can simplify each individual Copy/paste series with one line of code. For example:

Range("j1").Select
Selection.Copy
Range("i1").Select
Selection.PasteSpecial Paste:=xlValues

can also be written this way:

range("i1").value = range("j1").value

This will shorten the length of your macro, and it should run quite a bit faster, as well.

Good Luck!

-Glenn
 
Upvote 0 Downvote
Single cell copying can also be written using a shortcut

[I1] = [J1]

To put the zero's onto your worksheet

[C2] = 0

Also get rid of the Application.CutCopyMode = False
 
Upvote 0 Downvote
Cheers Glenn TB, Mad For It.

Not worked my way through it yet, but thats just the trick if it goes faster!

Is there not a way though of just repeating the code for row one to the row 2 to 600 or the number of rows that as data in it?

Thanks,

Andrew
 
Upvote 0 Downvote
APElliott,

The following sub routine will loop through a named range rng and copy the contents of each cell to the cell next to it.
Public Sub Copy_Range()

Dim rng As Range
Dim cell As Object
Set rng = ActiveSheet.Range("g3:g33")

For Each cell In rng
cell.Offset(0, 1).Value = cell.Value
Next cell
End Sub

with some modification this could do what you are looking for.

Tom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
984
SkipVought
S
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
1K
strongm
strongm
remeng
  • Locked
  • Question
Multiple Users in a Macro enabled Excel Doc
Replies
0
Views
427
remeng
remeng
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
657
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top