| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still Stuck
forgive me....forgive me.....but i am still unable to
figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
Offhand, it looks like the line
Selection.AutoFill Destination:=Range("B1:B" & Range("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
thanks, bob.......but no luck just yet....
i followed your suggestion....and when i ran the macro i got this error message: Runtime error '1004' Autofill method of Range class failed. it showed up on this line: Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault here is my new and improved macro with your changes included: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault Range("B1:B" & _ Range("B65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & _ Range("I65536").End(xlUp).Row), Type:=xlFillDefault Range("I1:I" & _ Range("I65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub -----Original Message----- Offhand, it looks like the line Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
Could it be there is nothing in col B?
BTW, did you bother to look at your original post on this with one possible answer that I gave. IMO, You should always stay in the original thread until solved. "Josh in Tampa" wrote in message ... thanks, bob.......but no luck just yet.... i followed your suggestion....and when i ran the macro i got this error message: Runtime error '1004' Autofill method of Range class failed. it showed up on this line: Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault here is my new and improved macro with your changes included: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault Range("B1:B" & _ Range("B65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & _ Range("I65536").End(xlUp).Row), Type:=xlFillDefault Range("I1:I" & _ Range("I65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub -----Original Message----- Offhand, it looks like the line Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
yes, i agree about sticking to one particular thread, but
i was growing weary and worried that noone was going to respond to it. honestly, i didn't exactly understand your response as well as i could have. i will go revisit it. there is, by the way, data in column B. thanks. -----Original Message----- Could it be there is nothing in col B? BTW, did you bother to look at your original post on this with one possible answer that I gave. IMO, You should always stay in the original thread until solved. "Josh in Tampa" wrote in message ... thanks, bob.......but no luck just yet.... i followed your suggestion....and when i ran the macro i got this error message: Runtime error '1004' Autofill method of Range class failed. it showed up on this line: Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault here is my new and improved macro with your changes included: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault Range("B1:B" & _ Range("B65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & _ Range("I65536").End(xlUp).Row), Type:=xlFillDefault Range("I1:I" & _ Range("I65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub -----Original Message----- Offhand, it looks like the line Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip (RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity (RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER (PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER (PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
apparently you don't understand your own code.
Once you do this Columns("B:B").Select Selection.Insert Shift:=xlToRight Column B is empty. you fill in the first cell, that is all that is in column b. When you do xldown, you fill down to the last row. -- Regards, Tom Ogilvy Josh in Tampa wrote in message ... yes, i agree about sticking to one particular thread, but i was growing weary and worried that noone was going to respond to it. honestly, i didn't exactly understand your response as well as i could have. i will go revisit it. there is, by the way, data in column B. thanks. -----Original Message----- Could it be there is nothing in col B? BTW, did you bother to look at your original post on this with one possible answer that I gave. IMO, You should always stay in the original thread until solved. "Josh in Tampa" wrote in message ... thanks, bob.......but no luck just yet.... i followed your suggestion....and when i ran the macro i got this error message: Runtime error '1004' Autofill method of Range class failed. it showed up on this line: Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault here is my new and improved macro with your changes included: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault Range("B1:B" & _ Range("B65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & _ Range("I65536").End(xlUp).Row), Type:=xlFillDefault Range("I1:I" & _ Range("I65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub -----Original Message----- Offhand, it looks like the line Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip (RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity (RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER (PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER (PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
Since column b is mostly empty when you do this, it doesn't make any
difference whether you go up or down. It isn't going to give you what you want. You were provided a solution so there is no use wasting time on providing additional help since you don't want to use it. -- Regards, Tom Ogilvy Josh in Tampa wrote in message ... thanks, bob.......but no luck just yet.... i followed your suggestion....and when i ran the macro i got this error message: Runtime error '1004' Autofill method of Range class failed. it showed up on this line: Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault here is my new and improved macro with your changes included: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault Range("B1:B" & _ Range("B65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & _ Range("I65536").End(xlUp).Row), Type:=xlFillDefault Range("I1:I" & _ Range("I65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub -----Original Message----- Offhand, it looks like the line Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End(xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Stuck
idiots and dumbasses are annoying, i know it's true.
i can see that you're a regular poster, and you've answered alot of my questions. i appreciate it. i'll come around......just need to find the time to study some of this. you're right about not wasting time with this.....if you say the answer is there, i'll go back and figure it out. thanks again, tom. -----Original Message----- Since column b is mostly empty when you do this, it doesn't make any difference whether you go up or down. It isn't going to give you what you want. You were provided a solution so there is no use wasting time on providing additional help since you don't want to use it. -- Regards, Tom Ogilvy Josh in Tampa wrote in message ... thanks, bob.......but no luck just yet.... i followed your suggestion....and when i ran the macro i got this error message: Runtime error '1004' Autofill method of Range class failed. it showed up on this line: Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault here is my new and improved macro with your changes included: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & _ Range("B65536").End(xlUp).Row), Type:=xlFillDefault Range("B1:B" & _ Range("B65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip(RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity(RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & _ Range("D65536").End(xlUp).Row), Type:=xlFillDefault Range("D1:D" & _ Range("D65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER(PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & _ Range("E65536").End(xlUp).Row), Type:=xlFillDefault Range("E1:E" & _ Range("E65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & _ Range("I65536").End(xlUp).Row), Type:=xlFillDefault Range("I1:I" & _ Range("I65536").End(xlUp).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub -----Original Message----- Offhand, it looks like the line Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault might take you to row 65536. That could cause the problem! Change it (and lines like it) to: Selection.AutoFill Destination:=Range("B1:B" & Range("B65536").End(xlUp).Row), Type:=xlFillDefault Bob Umlas Excel MVP "Josh in Tampa" wrote in message ... forgive me....forgive me.....but i am still unable to figure out how to fix my problem. let me try again to explain: when i run this macro (see below), everything works fine except for the fact that i am left with a humongous worksheet. if i try to print the job, the printer spits out a tree's worth of blank paper after it finishes the pages i need........why?......because it's printing the entire worksheet, i suppose. after running the macro, the scroll bar controller shrinks down to its smallest possible size, and it takes forever to scroll down to the bottom of the worksheet. if you have the time would you please review the macro below and let me know how it might be altered so that i am not left with such a huge worksheet? i've already posted this once, and to those of you who have responded.....THANK YOU very much....it is my fault that i'm such a moron. to those of you who might help me now, thanks in advance!! here's the macro: Sub CleanUp() ' ' CleanUp Macro ' Macro recorded 10/27/2003 by jbrady ' ' Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1)) Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Range("B1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & Range ("B1").End(xlDown).Row), Type:=xlFillDefault Range("B1:B" & Range("B1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Range("B1").Select Selection.Copy Range("D1").Select ActiveSheet.Paste Range("D1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("B:B").Select Selection.Copy Columns("A:A").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("C:C").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range("E1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip (RC[- 1])" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("D1").Select ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity (RC[- 1])" Range("D1").Select ActiveCell.FormulaR1C1 = "=TRIM(PROPER (PERSONAL.XLS! getcity(RC[-1])))" Range("D1").Select Selection.AutoFill Destination:=Range("D1:D" & Range ("D1").End(xlDown).Row), Type:=xlFillDefault Range("D1:D" & Range("D1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 ActiveWindow.SmallScroll ToRight:=1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1").Select ActiveCell.FormulaR1C1 = "=TRIM(UPPER (PERSONAL.XLS! getstate(RC[-2])))" Range("E1").Select Selection.AutoFill Destination:=Range("E1:E" & Range ("E1").End(xlDown).Row), Type:=xlFillDefault Range("E1:E" & Range("E1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("E:E").Select Selection.Insert Shift:=xlToRight Columns("F:F").Select Selection.Copy Columns("E:E").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("F:F").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=-3 Columns("C:C").Select Columns("D:D").Select Selection.Insert Shift:=xlToRight Columns("E:E").Select Selection.Copy Columns("D:D").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("E:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=1 Columns("G:G").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=75 ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Insert Shift:=xlToRight Range("I1").Select ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I" & Range ("I1").End(xlDown).Row), Type:=xlFillDefault Range("I1:I" & Range("I1").End (xlDown).Row).Select ActiveWindow.ScrollRow = 1 Columns("I:I").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("I:I").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("H:H").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=358 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.ScrollColumn = 1 End Sub . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I am stuck | Excel Worksheet Functions | |||
| Stuck! Please help | Excel Worksheet Functions | |||
| Im stuck again... | Excel Discussion (Misc queries) | |||
| Hmm Still stuck | Excel Discussion (Misc queries) | |||
| Stuck... | Excel Discussion (Misc queries) | |||
Hybrid Mode
