Tuesday Treachery

J

Josh in Tampa

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
 
T

Tom Ogilvy

You misread my post. I told you how to solve your problem.

If you are concerned about wasted space, then you should take the following
actions:

At the top of your macro . . .

Is that clearer.

--
Regards,
Tom Ogilvy

Josh in Tampa said:
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
Click to expand...
 
G

Guest

thanks, tom. went back and followed your
instructions......

at the bottom of my page, the code i inserted is showing
up in bright red font. here's the snippet:

Range(Range(sAddr),Cells(rows.count,1).EntireRow.Delete
ActiveSheet.UsedRange

the top line is showing up in red. i think it's missing
a ) but i don't know where to put it. thanks.
-----Original Message-----
You misread my post. I told you how to solve your problem.

If you are concerned about wasted space, then you should take the following
actions:

At the top of your macro . . .

Is that clearer.

--
Regards,
Tom Ogilvy

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
Click to expand...


.
Click to expand...
 
T

Tom Ogilvy

Sub Tester1()
Dim sAddr As String
sAddr = Cells(1, 1).End(xlUp)(2).Address

' your code

Range(Range(sAddr), _
Cells(Rows.Count, 1)).EntireRow.Delete
ActiveSheet.UsedRange

End Sub


--
Regards,
Tom Ogilvy

thanks, tom. went back and followed your
instructions......

at the bottom of my page, the code i inserted is showing
up in bright red font. here's the snippet:

Range(Range(sAddr),Cells(rows.count,1).EntireRow.Delete
ActiveSheet.UsedRange

the top line is showing up in red. i think it's missing
a ) but i don't know where to put it. thanks.
-----Original Message-----
You misread my post. I told you how to solve your problem.

If you are concerned about wasted space, then you should take the following
actions:

At the top of your macro . . .

Is that clearer.

--
Regards,
Tom Ogilvy

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
Click to expand...


.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Ok, there was a typo in the code: - my mistake.

Sub Tester1()
Dim sAddr As String
sAddr = Cells(rows.count, 1).End(xlUp)(2).Address

' your code

Range(Range(sAddr), _
Cells(Rows.Count, 1)).EntireRow.Delete
ActiveSheet.UsedRange

End Sub

--
Regards,
Tom Ogilvy

in an effort to get it to work.....

i used this at the bottom of my page:

Range((sAddr), Cells(Rows.Count, 1)).EntireRow.Delete
ActiveSheet.UsedRange

now, everything but the very top row of my worksheet gets
deleted. yikes. ; )
-----Original Message-----
You misread my post. I told you how to solve your problem.

If you are concerned about wasted space, then you should take the following
actions:

At the top of your macro . . .

Is that clearer.

--
Regards,
Tom Ogilvy

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
Click to expand...


.
Click to expand...
Click to expand...
 
J

Josh in Tampa

ahhhhhhh, relief!!!! thank you very much, tom. your
excel mastery is surpassed only by your patience, my
friend!! thank you.

josh
-----Original Message-----
Ok, there was a typo in the code: - my mistake.

Sub Tester1()
Dim sAddr As String
sAddr = Cells(rows.count, 1).End(xlUp)(2).Address

' your code

Range(Range(sAddr), _
Cells(Rows.Count, 1)).EntireRow.Delete
ActiveSheet.UsedRange

End Sub

--
Regards,
Tom Ogilvy

in an effort to get it to work.....

i used this at the bottom of my page:

Range((sAddr), Cells(Rows.Count, 1)).EntireRow.Delete
ActiveSheet.UsedRange

now, everything but the very top row of my worksheet gets
deleted. yikes. ; )
-----Original Message-----
You misread my post. I told you how to solve your problem.

If you are concerned about wasted space, then you
Click to expand...
should
take the following
actions:

At the top of your macro . . .

Is that clearer.

--
Regards,
Tom Ogilvy

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
Click to expand...
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
Click to expand...
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.
Click to expand...
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





.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

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

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

Ask a Question

Similar Threads

Still Stuck 9
Macro should Save and Overwrite file 1
variable number of rows. 2
Different Results from the Same Macro 2
Complie Syntax Error 6
fixing a run-time error'9': Subscript out of range 0
Copying Data Down the Column But Only Based on What's in Column A 1
excel macro missing worksheets 9

Top