Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Tuesday Treachery
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default 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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default 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





.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default 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





.



.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to calculate the next Tuesday Lee Perez Excel Discussion (Misc queries) 5 April 21st 23 02:53 PM
Tuesday JDay Excel Discussion (Misc queries) 1 February 3rd 10 09:58 PM
dates and 1st Tuesday of month Jessica[_2_] Excel Worksheet Functions 11 July 17th 07 11:13 PM
Every 1st, 3rd, 5th Tuesday Rick[_3_] Excel Worksheet Functions 9 June 12th 07 02:05 PM
12/31/2005 = TUESDAY? CLR Excel Discussion (Misc queries) 5 January 3rd 06 07:02 PM


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement