Code Help

S

Scottmk

First of all, I would like to explain what I am trying to do. I hav
Many columns of data, with a blank column set up next to each one.
was scanning the data for empty cells and placing "-" in the colum
next to empty, if not empty, placing the value instead. Then, it wen
back and deleted all of the old columns. I did a record macro. Now
my problem is my data is longer (in terms of rows) and the macro is n
good. The code looks insane, with all kinds of scrolls in it. Coul
someone look at the code and give some advice on making it simpler?
Thank you

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,""-"",RC[-1])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D157")
Range("D2:D157").Select
Range("D2").Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F2:F157")
Range("F2:F157").Select
Range("F2").Select
Selection.Copy
Range("H2").Select
ActiveSheet.Paste
Range("J2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Range("L2").Select
ActiveSheet.Paste
Range("N2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("P2").Select
ActiveSheet.Paste
Range("R2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("T2").Select
ActiveSheet.Paste
Range("V2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Range("X2").Select
ActiveSheet.Paste
Range("Z2").Select
ActiveSheet.Paste
Range("AB2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("AD2").Select
ActiveSheet.Paste
Range("AF2").Select
ActiveSheet.Paste
Range("AH2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("AJ2").Select
ActiveSheet.Paste
Range("AL2").Select
ActiveSheet.Paste
Range("AM37").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-0.839999973773956"
Range("AL2").Select
Selection.Copy
Range("AN2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=2
Range("AP2").Select
ActiveSheet.Paste
Range("AR2").Select
ActiveSheet.Paste
Range("AT2").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-15
Range("H2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H2:H157")
Range("H2:H157").Select
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J157")
Range("J2:J157").Select
ActiveWindow.SmallScroll ToRight:=3
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L157")
Range("L2:L157").Select
ActiveWindow.SmallScroll ToRight:=3
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N157")
Range("N2:N157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:p157")
Range("P2:p157").Select
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R157")
Range("R2:R157").Select
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T157")
Range("T2:T157").Select
ActiveWindow.SmallScroll ToRight:=5
Range("V2").Select
Selection.AutoFill Destination:=Range("V2:V157")
Range("V2:V157").Select
Range("X2").Select
Selection.AutoFill Destination:=Range("X2:X157")
Range("X2:X157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z157")
Range("Z2:Z157").Select
Range("AB2").Select
Selection.AutoFill Destination:=Range("AB2:AB157")
Range("AB2:AB157").Select
ActiveWindow.SmallScroll ToRight:=5
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD157")
Range("AD2:AD157").Select
Range("AF2").Select
Selection.AutoFill Destination:=Range("AF2:AF157")
Range("AF2:AF157").Select
Range("AH2").Select
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("AH2:AH157")
Range("AH2:AH157").Select
Range("AJ2").Select
Selection.AutoFill Destination:=Range("AJ2:AJ157")
Range("AJ2:AJ157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AL2").Select
Selection.AutoFill Destination:=Range("AL2:AL157")
Range("AL2:AL157").Select
Range("AN2").Select
Selection.AutoFill Destination:=Range("AN2:AN157")
Range("AN2:AN157").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AP2").Select
Selection.AutoFill Destination:=Range("AP2:AP157")
Range("AP2:AP157").Select
Range("AR2").Select
Selection.AutoFill Destination:=Range("AR2:AR157")
Range("AR2:AR157").Select
Range("AT2").Select
Selection.AutoFill Destination:=Range("AT2:AT157")
Range("AT2:AT157").Select
 
S

Scottmk

The rest:

ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=5
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=5
Columns("P:p").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=5
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("X:X").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
Columns("Z:Z").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("AB:AB").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("AD:AD").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Columns("AF:AF").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("AH:AH").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Columns("AJ:AJ").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AL:AL").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Columns("AN:AN").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AP:AP").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AR:AR").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("AT:AT").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
ActiveWindow.SmallScroll ToRight:=9
Selection.Delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Columns("M:M").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("P:p").Select
Selection.Delete Shift:=xlToLeft
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("R:R").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=5
Columns("S:S").Select
Selection.Delete Shift:=xlToLeft
Columns("T:T").Select
Selection.Delete Shift:=xlToLeft
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Columns("V:V").Select
Selection.Delete Shift:=xlToLeft
Columns("W:W").Select
Selection.Delete Shift:=xlToLeft
Columns("X:X").Select
Selection.Delete Shift:=xlToLeft
End Sub
 
S

Scottmk

Actually, is there any way to just make a blank cell contain "-". Lik
in the custom format field
 
D

Dianne Butterworth

Not through custom formats, but you could use Replace.

Select your entire area from D2:AT157 then select Edit > Replace from the
menu.
Make sure the Find What box is empty, and type your hyphen into the Replace
With box.

I tried this with "Make sure that "Find Entire Cells Only" ticked and again
with it unticked and it didn't seem to make any difference, although I would
have thought it would have. Huh.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro that deletes and pastes filterd rows and then some 2
Macro code 0
PLEASE HELP...How do I update a pivot table with VBA? 0
VBScipt: Can you hrlp me automate this ? 2
run macro text missing 1
Create new workbook when macro is run 2
same macro on multiple sheets 4
vLOOK UP INTO vba 3

Top