okay. i should probably start by apologizing to any and
all who feel as if i've wasted their time and efforts
yesterday. i really wasn't trying to do so, and i'll do
my best to make sure it doesn't happen again. if i've
been banished to some deep corner of this newsgroup, never
to be acknowledged again.....that's regrettable because i
really need some help here. ; )

i've spent the early part of this morning going back and
reading all the posts associated with my difficulties in
getting my macro to run properly. i'm still confused and
unsure as to how to find my way out of this tangled mess.

original problem: after running my macro, my worksheet is
humongous.....something like 65000 records. i'm not
worried about eliminating the wasted space at the top of
my worksheet (2 rows).......i'm worried about finding a
way to eliminate the seemingly endless rows of blankness
that follows. what's funny is that it is always possible
to scroll down to these depths, but usually, these endless
rows of nothingness are not sent to the printer for print
jobs. sigh.

original fix: convert lines of macro code from:

Range("B1:B" & Range("B1").End(xlDown).Row).Select

to:

Range("B1:B" & Range("B65536").End(xlUp).Row).Select

this original fix generated an error:

Runtime error '1004'
Autofill method of Range class failed.

it was explained to me that this was because Column B was
empty.

and so, basically, this is where i sit. stuck. unable to
wrap my mind around this problem. it's very frustrating.

i'm feeling a little bit like i'm at my wits end, AND that
i've exhausted my chances at getting you guys to help me
out. i apologize again for being a brick-headed mule.

here's my 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("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