Remove commented lines

[richarddawson]

I have an excel spreadsheet containing many rows copied from VBE.

Some of these contain comments - always with a ' in column 1. I would like to delete these lines, but the ' is a hidden character and the program can not find it.

Any suggestions, please?

 

[Yongle]

try this on a COPY of your workbook

Sub RemoveHiddenApos()
    Dim Rng As Range, Cell As Range, Del As Range, Ws As Worksheet
    Set Ws = ActiveSheet
    Set Rng = Ws.Columns("A").SpecialCells(xlCellTypeConstants)
    Set Del = Ws.Cells(Ws.Rows.Count, 1)
    
    For Each Cell In Rng
        If Cell.PrefixCharacter = "'" Then Set Del = Union(Del, Cell)
    Next Cell
    Del.EntireRow.Delete
End Sub

 

[richarddawson]

Thanks Yongle - super stuff!

Works perfectly.

Richard