CODEWITHSUNDEEP
vbaexcel
D Schlachter
D Schlachter

Reputation: 221

VBA Excel — Select Range from String

I need to select all rows smaller than 16 points in order to manually delete them. Programatically deleting them rescales images on the spreadsheet and breaks it (ChemBio generated chemical structures).

My code works up until it makes the selection:

Sub FindAndRemoveSmallRows()
Dim a As Range, b As Range, c As String
Set a = Selection
For Each b In a.Rows
    If b.Height < 16 Then
        c = c & b.Row & ":" & b.Row & ","
    End If
Next
If Right$(c, 1) = "," Then c = Left$(c, Len(c) - 1)
Range(c).Select
End Sub

How can I pass the string (which outputs, e.g., "67:67,513:513,534:534") to Range in order to select the rows?

Upvotes: 0

Views: 1071

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

This may work for you:

Sub FindAndRemoveSmallRows()
Dim b As Range, c As Range
if typename(selection)<>"Range" then exit sub
For Each b In Selection.Rows
    If b.Height < 16 Then
        if c is nothing then 
            set c = b
        else
            set c = application.union(c, b)
        end if
    End If
Next
if not c is nothing then c.entirerow.Select
End Sub

Upvotes: 1

Related Questions