[VBA] clean up empty code line in VBE under Sub / Function

[smallxyz]

Very Often I create many blank codes lines during VBA coding.

Is there available add-in to clean up those empty lines, to generate a more neatly-aligned VBA code?


Thanks!
e.g.

Sub XYZ()
　Dim A as Integer
　(empty)
　(empty)
　A = A + 1
　(empty)
　MsgBox A
　(empty)
　(empty)
End Sub

Thanks a lot!

 

[Fazza]

so, simply delete them?

 

[tonyyy]

Though not an add-in, you can use the following to delete empty code lines...

Sub DeleteBlankLinesInVBE()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This deletes blank code lines in the specified module
' And also counts the total number of lines
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
Dim S As String
Dim LineCount As Long
Dim wb As String
Dim Answer As String
Dim VBComp As VBIDE.VBComponent

wb = InputBox(prompt:="Please enter a workbook name.", Title:="Delete Blank Lines", Default:="Book1")
Answer = InputBox(prompt:="Please enter a module number.", Title:="Delete Blank Lines", Default:="Module1")
Set VBComp = Workbooks(wb).VBProject.VBComponents(Answer)

With VBComp.CodeModule
    For N = .CountOfLines To 1 Step -1
        S = .Lines(N, 1)
        If Trim(S) = vbNullString Then
            .DeleteLines N
        ElseIf Left(Trim(S), 1) = "'" Then
            ' comment line, skip it
        Else
            LineCount = LineCount + 1
        End If
    Next N
End With

Debug.Print LineCount - 1
End Sub

Cheers,

tonyyy

 

[Fazza]

I was going to suggest it should be easy enough to write some code for that. i'm sure i've done it in the past

via google just now I found Andrew's Excel Tips: AET VBE Tools 1.7.1
which seems to do what you want. I've never tried it

if not, try googling again

 

[Fazza]

Tonyyy. That was quick. thanks

 

[tonyyy]

Fazza - Just copied it from my personal.xlsb; something I use when encountering posts with lots of blank lines.

 

[Fazza]

i have some code for indenting. but it doesn't currently delete blank lines. maybe I'll add that

one comment from my code, relevant here, is

' REQUIRED REFERENCE : Microsoft Visual Basic for Applications Extensibility 5.3

 

[smallxyz]

Guys, thank you so much!
How long have you been immersed in using Excel VBA?
Hope one day I create such codes myself.
Thanks a lot!

 

[smallxyz]

The code works well!
One question. is it possible to create a shortcut key for it, such that, during VBE coding, it can be executed upon triggering the shortcut keys?

Thanks!

 

[tonyyy]

The code works well!
One question. is it possible to create a shortcut key for it, such that, during VBE coding, it can be executed upon triggering the shortcut keys?

Thanks!

You're welcome...

Sorry, don't know about creating a shortcut key in the vbe. You could, I suppose, add an icon to the QAT and launch it from there.