VBA Excel — Select Range from String

Question

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?

As I see it won't work only when all rows in Selection has height >= 16 and since c is empty Range(c).Select throws an error. Can you add MsgBox c just before Range(c).Select. What message would you get? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 4, 2014 at 20:51
I used ActiveSheet.Range("C1").Value = c instead; prints 904:904,928:928,968:968,969:969,992:992,1035:1035. — D Schlachter
– D Schlachter, Commented Mar 4, 2014 at 20:53
What happend when code tries to evaluate Range(c).Select? Did you get any error? If yes, what is the error message? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 4, 2014 at 20:55
It prints, Run-time error '1004': Method 'Range' of object '_Global' failed. — D Schlachter
– D Schlachter, Commented Mar 4, 2014 at 20:58

Tim Williams · Accepted Answer · 2014-03-04 21:00:37Z

1

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

answered Mar 4, 2014 at 21:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel — Select Range from String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related