Delete specific VBA code lines

[matttclark]

Hello,

As is frequently reccommended, I use the macro recorder as a starting place to try and figure out new thingsI'm trying to do.

Has or could anyone has come up with a small utitlity to clean what I'll call 'junk code' from macro recorder code. The most common example in my experience is code for scrolling, e.g. see the sample below. Its tedious to manually clean this out.

Range("F53").Select
    ActiveSheet.Range("$F$52:$I$57").AutoFilter Field:=1, Criteria1:= _
        "DTFACT-09-D-00010"
    Range("G47").Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.SmallScroll Down:=15
    ActiveSheet.Range("$F$52:$I$57").AutoFilter Field:=1
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=30
    Range("F93").Select

Thanks for any help!

 

[Andrew Poulsom]

Not the answer you want, but the easiest way is not to scroll when recording. I don't see why you need to scroll at all when recording those actions.

 

[matttclark]

Hi Andrew,

Forgive me if I'm a little slow this morning but I'm not sure how to get around that?

If I dump using a mouse (yikes in itself !) to use just keyboard navigation won't I just get a lot of different 'junk code', e.g,

Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select

Thanks!

 

[Andrew Poulsom]

This is what I get if I record the AutoFilter:

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 10/02/2012 by Andrew
'
'
    Range("F52:I57").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="DTFACT-09-D-00010"
    Selection.AutoFilter Field:=1
End Sub

There is no scrolling because i didn't move my mouse wheel.

 

[matttclark]

Andrew, yes that is correct if I just do a single filter action. Sorry if my examples gave the wrong impression, they were just meant to be examples of what I called the 'junk code' not details of all the actions I may record at a given time.

I am usually performing mulitple actions and frequently on mulitple ranges, hence the need to move around.

Thx.

 

[Andrew Poulsom]

I just record discrete actions and join the resulting code together.

 

[matttclark]

Yes I had considered that, but in my case that could end up taking more time than deleting all the 'scroll' lines. We all have different needs don't we!

Andrew, thanks for your thoughts and time, I have used the advice in many of your posts before. I'll keep searching and/or hope someone has had a need to do a search and replace type activity on their code before similar to what I am looking for.

Also, I just found another potential use for this type of feature as well.

If anyone is using the Office Code Compatibility Inspector (OCCI) for Office 2010, it can add comments similar to this to anything it finds in your code:

[QUOTE]'
        '   
        '       Potentially contains deprecated items in the object model
        '       [mso]ChartFont.ColorIndex
        '       http://go.microsoft.com/fwlink/?LinkID=215358 /URL>
        '   
        '[/QUOTE]

When you are done revising your code, it would be nice to be able to automatically clean it up similar to what I was origianlly inquiring about....

 

[matttclark]

ahhh, i am having difficulty pasting the exact look of the added comments from the Office Compatibility Inspector.

try again...

'<VBA_INSPECTOR>
' <DEPRECATION>
' <MESSAGE>Potentially contains deprecated items in the object model</MESSAGE>
' <ITEM>[mso]ChartFont.ColorIndex</ITEM>
' <URL>http://go.microsoft.com/fwlink/?LinkID=215358 /URL>
' </DEPRECATION>
'</VBA_INSPECTOR>

 

[matttclark]

hmmm, can't get it to post correctly, it keeps dropping the 'tags'

see paragrpah 3.c under this link for example of what I am talking about

 

[Norie]

Do you have a set of rules to use to determine what to delete from the code and what to keep?