Thread: Still Stuck
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
  
Posts: 27,285
Default Still Stuck
If all you are worried about is the extraneous information/size, at the top
of your code do this (treat the symptoms)

Dim sAddr as String
sAddr = Cells(1,1).End(xlup)(2).Address


at the very bottom of your code put in this


Range(Range(sAddr),Cells(rows.count,1).EntireRow.D elete
Activesheet.Usedrange

Then, after your code has run, save your file and your problem should be
fixed. color it done.

My problem with you is that you asked people to invest time to understand
and solve your problem based on reading through a long list of recorder
code, then came back 10 or more hours later with a new recorded macro. And
you have no intention of using what you asked for. Posting 8 hours after
the solutions were posted and saying you were concerned you wouldn't get an
answer just emphasizes your lack of (fill in any negative comment here).

--
Regards,
Tom Ogilvy


Josh in Tampa wrote in message
...
humbled and grateful. it is true that i don't exactly
understand the code. however........i would like to add
that i seem to grasp the procedural languages okay, and
i'm not so bad with object-oriented concepts, etc., but
these vba macros, i find them a little confusing and hard
to read. i'm okay with building the user defined
functions, but like i said, the macros just seem to have
me befuddled. i need to sit down and study it harder.

thanks again for your help, all.
-----Original Message-----
apparently you don't understand your own code.

Once you do this

Columns("B:B").Select
Selection.Insert Shift:=xlToRight

Column B is empty.

you fill in the first cell, that is all that is in column
b. When you do
xldown, you fill down to the last row.

--
Regards,
Tom Ogilvy



Josh in Tampa wrote
in message
...
yes, i agree about sticking to one particular thread,
but
i was growing weary and worried that noone was going to
respond to it.

honestly, i didn't exactly understand your response as
well as i could have. i will go revisit it.

there is, by the way, data in column B.

thanks.
-----Original Message-----
Could it be there is nothing in col B?
BTW, did you bother to look at your original post on
this
with one possible
answer that I gave.
IMO, You should always stay in the original thread
until
solved.

"Josh in Tampa"
wrote in message
...
thanks, bob.......but no luck just yet....

i followed your suggestion....and when i ran the
macro i
got this error message:

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

it showed up on this line:

Selection.AutoFill Destination:=Range("B1:B" & _
Range("B65536").End(xlUp).Row), Type:=xlFillDefault

here is my new and improved macro with your changes
included:

Sub CleanUp()
'
' CleanUp Macro
' Macro recorded 10/27/2003 by jbrady
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False,
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3,
1),
Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1))
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Columns("A:A").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Range("B1").Select
ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & _
Range("B65536").End(xlUp).Row), Type:=xlFillDefault
Range("B1:B" & _
Range("B65536").End(xlUp).Row).Select
ActiveWindow.ScrollRow = 1
Range("B1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-1]))"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D" & _
Range("D65536").End(xlUp).Row), Type:=xlFillDefault
Range("D1:D" & _
Range("D65536").End(xlUp).Row).Select
ActiveWindow.ScrollRow = 1
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("E1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!trimzip
(RC[-
1])"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" & _
Range("E65536").End(xlUp).Row), Type:=xlFillDefault
Range("E1:E" & _
Range("E65536").End(xlUp).Row).Select
ActiveWindow.ScrollRow = 1
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getcity
(RC[-
1])"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=TRIM(PROPER
(PERSONAL.XLS!
getcity(RC[-1])))"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D" & _
Range("D65536").End(xlUp).Row), Type:=xlFillDefault
Range("D1:D" & _
Range("D65536").End(xlUp).Row).Select
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=1
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "=TRIM(UPPER
(PERSONAL.XLS!
getstate(RC[-2])))"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" & _
Range("E65536").End(xlUp).Row), Type:=xlFillDefault
Range("E1:E" & _
Range("E65536").End(xlUp).Row).Select
ActiveWindow.ScrollRow = 1
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=-3
Columns("C:C").Select
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=1
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=75
ActiveWindow.ScrollRow = 1
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC[1])"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I" & _
Range("I65536").End(xlUp).Row), Type:=xlFillDefault
Range("I1:I" & _
Range("I65536").End(xlUp).Row).Select
ActiveWindow.ScrollRow = 1
Columns("I:I").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("H:H").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=358
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
ActiveWindow.ScrollColumn = 1
End Sub

-----Original Message-----
Offhand, it looks like the line
Selection.AutoFill Destination:=Range("B1:B" & Range
("B1").End(xlDown).Row),
Type:=xlFillDefault
might take you to row 65536. That could cause the
problem! Change it (and
lines like it) to:
Selection.AutoFill Destination:=Range("B1:B" &
Range("B65536").End(xlUp).Row), Type:=xlFillDefault

Bob Umlas
Excel MVP



"Josh in Tampa"

wrote in message
...
forgive me....forgive me.....but i am still
unable to
figure out how to fix my problem. let me try
again
to
explain:

when i run this macro (see below), everything
works
fine
except for the fact that i am left with a
humongous
worksheet. if i try to print the job, the printer
spits
out a tree's worth of blank paper after it
finishes
the
pages i need........why?......because it's
printing
the
entire worksheet, i suppose.

after running the macro, the scroll bar controller
shrinks
down to its smallest possible size, and it takes
forever
to scroll down to the bottom of the worksheet.

if you have the time would you please review the
macro
below and let me know how it might be altered so
that i
am
not left with such a huge worksheet?

i've already posted this once, and to those of you
who
have responded.....THANK YOU very much....it is my
fault
that i'm such a moron. to those of you who might
help
me
now, thanks in advance!! here's the macro:

Sub CleanUp()
'
' CleanUp Macro
' Macro recorded 10/27/2003 by jbrady
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range
("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True,
Space:=False,
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3,
1),
Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1))
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Columns("A:A").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Range("B1").Select
ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-
1]))"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" &
Range
("B1").End(xlDown).Row), Type:=xlFillDefault
Range("B1:B" & Range("B1").End
(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Range("B1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TRIM(PROPER(RC[-
1]))"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D" &
Range
("D1").End(xlDown).Row), Type:=xlFillDefault
Range("D1:D" & Range("D1").End
(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("E1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!
trimzip
(RC[-
1])"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" &
Range
("E1").End(xlDown).Row), Type:=xlFillDefault
Range("E1:E" & Range("E1").End
(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!
getcity
(RC[-
1])"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=TRIM(PROPER
(PERSONAL.XLS!
getcity(RC[-1])))"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D" &
Range
("D1").End(xlDown).Row), Type:=xlFillDefault
Range("D1:D" & Range("D1").End
(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=1
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "=TRIM(UPPER
(PERSONAL.XLS!
getstate(RC[-2])))"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" &
Range
("E1").End(xlDown).Row), Type:=xlFillDefault
Range("E1:E" & Range("E1").End
(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=-3
Columns("C:C").Select
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=1
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=75
ActiveWindow.ScrollRow = 1
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "=LOWER(RC[-1]&RC
[1])"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I" &
Range
("I1").End(xlDown).Row), Type:=xlFillDefault
Range("I1:I" & Range("I1").End
(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("I:I").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("H:H").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=358
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
ActiveWindow.ScrollColumn = 1
End Sub



.



.



.



Reply With QuoteReply With Quote