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

feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
162
SkipVought
S
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
161
strongm
strongm
remeng
  • Locked
  • Question
Multiple Users in a Macro enabled Excel Doc
Replies
0
Views
76
remeng
remeng
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
121
mintjulep
M
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
162
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top