ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Tuesday Treachery (https://www.excelbanter.com/excel-programming/280767-tuesday-treachery.html)

Josh in Tampa
Tuesday Treachery
 
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

Tom Ogilvy
Tuesday Treachery
 
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" wrote in message
...
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

No Name
Tuesday Treachery
 
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.D elete
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

"Josh in Tampa"
wrote in message
...
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





.

Tom Ogilvy
Tuesday Treachery
 
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

wrote in message
...
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.D elete
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

"Josh in Tampa"
wrote in message
...
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





.

Tom Ogilvy
Tuesday Treachery
 
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

wrote in message
...
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

"Josh in Tampa"
wrote in message
...
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





.

Josh in Tampa
Tuesday Treachery
 
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

wrote in message
...
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

"Josh in Tampa"
wrote in message
...
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





.



.


All times are GMT +1. The time now is 02:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com