Ok, I am going crazy. Below is a macro I recorded and stored in
Personal.xls, in its entirety. It fixes the garbage that comes in from
an html data source that I must use frequently (online enterprise
software). It seems to cause problems, and I don't know why, it
appears fine and simple. Is there anything wrong with this? Or is this
just SOP for Excel and other people have the same problems? I would
appreciate any insight. (WinXP, Excel 2003, Dell Optiplex)

This is my workflow:
I copy/paste data, run the macro, copy the data to a preformated area
in the same workbook to look pretty, then delete the raw data.

I have a coworker with the same job. She does the same workflow,
except doing it slowly without a macro. None of these problems surface
for her.

Here are the problems I have after running this macro:

1) Redo/Repeat stops working. It says "Repeat Macros" in the Edit
menu, but doesn't actually DO anything when selected. It never changes
from "Repeat Macros", so the Repeat command is lost til I restart
Excel. Menu selection, F4, CTRL-Y, nothing works.
2) It affects the size of the file. I always delete the entire columns
where the download was pasted, yet the workbook keeps growing as if I
leave the full raw data in there every single time I do this. After a
few months, the file has quadrupled in size.
3) It 'sticks' to the file somehow. When I send files to certain
companies, their over-zealous security deletes any emails with
attachments containing macros. These files do not contain this (or
any) macro, it is stored in Personal. Yet the firewalls claim there is
a macro after I run it on these files, and block my emails.


Code:

Sub Clean()
'
' Clean Macro
' Macro recorded 1/25/2007
'

'
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
ActiveSheet.DrawingObjects.Delete
Cells.Select
Selection.UnMerge
Selection.WrapText = False
Range("B:IV").Select
Selection.Style = "Comma"
Range("A1").Select

End Sub

On Tue, 22 Jul 2008 11:49:30 -0700 (PDT), Spiky <(E-Mail Removed)>
wrote:

Well, it's not the most efficient code, but it's not causing those problems.

>
>Here are the problems I have after running this macro:
>
>1) Redo/Repeat stops working. It says "Repeat Macros" in the Edit
>menu, but doesn't actually DO anything when selected. It never changes
>from "Repeat Macros", so the Repeat command is lost til I restart
>Excel. Menu selection, F4, CTRL-Y, nothing works.

Running *any* macro kills the undo stack so you can't undo/repeat. Once
it's done, however, it should be back working, just without any entries from
before the macro was run.

>2) It affects the size of the file. I always delete the entire columns
>where the download was pasted, yet the workbook keeps growing as if I
>leave the full raw data in there every single time I do this. After a
>few months, the file has quadrupled in size.

Maybe doing the Unmerge and Wrap on every cell is causing it. I'm not sure
on that, but see the macro below.

>3) It 'sticks' to the file somehow. When I send files to certain
>companies, their over-zealous security deletes any emails with
>attachments containing macros. These files do not contain this (or
>any) macro, it is stored in Personal. Yet the firewalls claim there is
>a macro after I run it on these files, and block my emails.

You probably have an empty module in the workbook. Open the VBE (Alt+F11)
and the Project Explorer (Ctl+R). Make sure there are no modules,
userforms, or class modules in the project. Also, open the Microsoft Excel
Objects (like Sheet1) and make sure there's no code in any of those.

>
>
>Code:
>
>Sub Clean()
>'
>' Clean Macro
>' Macro recorded 1/25/2007
>'
>
>'
> Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart,
>SearchOrder _
> :=xlByRows, MatchCase:=False, SearchFormat:=False,
>ReplaceFormat:=False
> ActiveSheet.DrawingObjects.Delete
> Cells.Select
> Selection.UnMerge
> Selection.WrapText = False
> Range("B:IV").Select
> Selection.Style = "Comma"
> Range("A1").Select
>
>End Sub

With ActiveSheet.UsedRange
.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
.UnMerge
.WrapText = False
End With
ActiveSheet.DrawingObjects.Delete
Intersect(ActiveSheet.Range("B:IV"), ActiveSheet.UsedRange).Style = "Comma"
--
Dick

> >1) Redo/Repeat stops working. It says "Repeat Macros" in the Edit
> >menu, but doesn't actually DO anything when selected. It never changes
> >from "Repeat Macros", so the Repeat command is lost til I restart
> >Excel. Menu selection, F4, CTRL-Y, nothing works.
>
> Running *any* macro kills the undo stack so you can't undo/repeat. Once
> it's done, however, it should be back working, just without any entries from
> before the macro was run.

That's what I would expect, and that would be fine. Yet it kills it
til Excel is quit. Of course, I'm trying to duplicate it this morning
for comparison and it isn't happening. But it certainly did yesterday
(and many, many other days), I had to quit Excel to get the
functionality back. So apparently it is not 100% of the time.

> >2) It affects the size of the file. I always delete the entire columns
> >where the download was pasted, yet the workbook keeps growing as if I
> >leave the full raw data in there every single time I do this. After a
> >few months, the file has quadrupled in size.
>
> Maybe doing the Unmerge and Wrap on every cell is causing it. I'm not sure
> on that, but see the macro below.

Thanks, I'll try your code, see if it improves. So, UsedRange makes it
work on just cells with data/formatting in them?

> >3) It 'sticks' to the file somehow. When I send files to certain
> >companies, their over-zealous security deletes any emails with
> >attachments containing macros. These files do not contain this (or
> >any) macro, it is stored in Personal. Yet the firewalls claim there is
> >a macro after I run it on these files, and block my emails.
>
> You probably have an empty module in the workbook. Open the VBE (Alt+F11)
> and the Project Explorer (Ctl+R). Make sure there are no modules,
> userforms, or class modules in the project. Also, open the Microsoft Excel
> Objects (like Sheet1) and make sure there's no code in any of those.

There isn't a module. There are the standard objects, one for each
sheet and the Workbook object. All empty. This only started late last
year. I don't know if the other companies upgraded hardware (I think
I've narrowed that side to Sonicwalls) then, or if it coincided with a
Microsoft update....

Thanks for your comments.

On Wed, 23 Jul 2008 09:18:38 -0700 (PDT), Spiky <(E-Mail Removed)>
wrote:

>> Running *any* macro kills the undo stack so you can't undo/repeat. Once
>> it's done, however, it should be back working, just without any entries from
>> before the macro was run.
>
>That's what I would expect, and that would be fine. Yet it kills it
>til Excel is quit. Of course, I'm trying to duplicate it this morning
>for comparison and it isn't happening. But it certainly did yesterday
>(and many, many other days), I had to quit Excel to get the
>functionality back. So apparently it is not 100% of the time.

Hmm, strange. If you had a Worksheet_Selection change event macro in an
open workbook at the time, it would clear the Undo stack every time you
clicked a cell. Otherwise, I can't think of what might cause that. If you
get any more clues next time it happens, post back.

>> Maybe doing the Unmerge and Wrap on every cell is causing it. I'm not sure
>> on that, but see the macro below.
>
>Thanks, I'll try your code, see if it improves. So, UsedRange makes it
>work on just cells with data/formatting in them?

Right. Sometimes Excel doesn't reset the UsedRange property and it's bigger
than it needs to be. But it's never smaller and it's almost always smaller
than the whole worksheet.

>>
>> You probably have an empty module in the workbook. Open the VBE (Alt+F11)
>> and the Project Explorer (Ctl+R). Make sure there are no modules,
>> userforms, or class modules in the project. Also, open the Microsoft Excel
>> Objects (like Sheet1) and make sure there's no code in any of those.
>
>There isn't a module. There are the standard objects, one for each
>sheet and the Workbook object. All empty. This only started late last
>year. I don't know if the other companies upgraded hardware (I think
>I've narrowed that side to Sonicwalls) then, or if it coincided with a
>Microsoft update....
>

You know, that reminds me. I get a file from someone who uses OpenOffice
that gives me a macro warning and there's no evidence of code in it at all.
--
Dick

Well, I've discovered one more detail. Problem #1 is not related to
this macro, I was apparently wrong before. This problem actually shows
up when I use the Subtotal command. And this is repeatable.