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!

 

[matttclark]

Hi Norie,

Based on my original post, it would be any code line that starts with

ActiveWindow.ScrollColumn

If your feeling up for a challenge based on the microsoft tool....I can't get the lines to display as I'm thinking they are being treated as XML tags in the web page. The tool adds what looks like an XML root and child elements as comments in the VBA.

Each time it finds a compatibility issue with a line of code it inserts several lines of comments that start with

'**VBA_INSPECTOR**

. The last line ends with

'**/VBA_INSPECTOR**

subtitute < or > for ** as the opening and closing brackets respectively

Hope this makes sense and either way, THANKS!

 

[Norie]

Why not just use Find/Replace?

There's an option that will allow you to use pattern matching.

So you could use wildcards to get rid of the code you don't need.

 

[matttclark]

yeah, the original thought was to have a handy module ready...always want to automate things!!!

with the new MS tool, not sure how to handle that

 

[matttclark]

oh sheesh and forgot the main reason for my original post ...i need to delete the entire line that STARTS with the referenced value...don't think the VBE search and replace can handle that

 

[Norie]

Are you sure?

 

[matttclark]

Norie,

No i'm not... LOL....thats why I'm asking.

If I use pattern matching and a "*" at the end up with a lot of blank lines. Nicer than the the 'junk' I refer to but still not what I am trying to accomplish which is delete an entire line.

And actually I'm really trying to see if there is a way to code this for repeated use. However, I don't even know if the VBE can be automated in this way. If you know, I'd appreciate a couple pointers in the right direction. All searches so far turn up references to editing excel sheets from the VBE or VBA. The best I found was a VBEproperty in the VBE help but that doesn't seem to take me anywhere.

So bottom line if this is not do-able then bummer but so be it. Thanks for the time and interest in any event.

 

[Norie]

Matt

In theory I suppose it is possible to do.

You would can access the VBE programatically to go through all the code.

I don't think I've ever seen it done though I'm sure there'll be something out there.

 

[chirp]

okay so i rarely work with the VBE through vba but here is something that should work.

please refer to this to learn how to set up your workbook so you can run this module.

basically you need to set up a reference to the extensibility pack, and make one change in your settings to "allow access to the vbe", probably a good idea to change this setting back after you are done!

Sub test()

Call delete_specific_lines("Module1", Array("ActiveWindow.ScrollColumn", "ActiveWindow.SmallScroll", "ActiveWindow.LargeScroll"))
End Sub

Public Function delete_specific_lines(moduleName As String, _
                                        targetString, _
                                        Optional cWb As Workbook = Nothing, _
                                        Optional tst As Boolean)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim delLine As Boolean, i As Long
Dim v

tst = False
On Error GoTo exitFunc

If cWb Is Nothing Then Set cWb = ActiveWorkbook
Set VBProj = cWb.VBProject
Set VBComp = VBProj.VBComponents(moduleName)
Set CodeMod = VBComp.CodeModule

'this just makes it easier to loop through...messy but w/e
If Not isArray(targetString) Then
    targetString = Array(CStr(targetString))
End If

With CodeMod
    For Each v In targetString
        i = 1
        Do
            delLine = .Find(CStr(v), i, 1, -1, -1)
            If delLine Then .DeleteLines (i)
        Loop While delLine
    Next
End With
tst = True
exitFunc:
End Function