find cells that begin a certain way and insert a blank line

Question

trying to insert a blank row between these values:

  Dim rngToFind As Range
  Dim strToFind As String
  Dim strFirstAddr As String
 
  strToFind1 = "Unit Specialty Topics"
  strToFind2 = "Professional Development Topics"
  strToFind3 = "Clinical Topics"
  strToFind4 = "Methodology"
 
  With Sheets("Sheet2").UsedRange
 
    Set rngToFind = .Find(What:=strToFind, _
      LookIn:=xlFormulas, _
      LookAt:=xlPart, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, _
      MatchCase:=False)
 
    If Not rngToFind Is Nothing Then
      strFirstAddr = rngToFind.Address

      Do
        If Not IsNumeric(rngToFind) Then
          Rows(rngToFind.Address).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    End If
      Set rngToFind = .FindNext(rngToFind)
      Loop While Not rngToFind Is Nothing And strFirstAddr <> rngToFind.Address
    End If
   
  End With
End Sub

Answer 1

Try this version:

Sub Test()
    Dim rngToFind As Range
    Dim rngFirstFind As Range
    Dim varToFind

    For Each varToFind In Array("Unit Specialty Topics", _
            "Professional Development Topics", _
            "Clinical Topics", _
            "Methodology")
        With Sheets("Sheet2").UsedRange
            Set rngFirstFind = .Find(What:=varToFind, _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)
            If Not rngFirstFind Is Nothing Then
                Set rngToFind = rngFirstFind
                Do
                    rngToFind.EntireRow.Insert
                    Set rngToFind = .FindNext(rngToFind)
                Loop While Not rngToFind Is Nothing And _
                    rngToFind.Address <> rngFirstFind.Address
            End If
        End With
    Next varToFind
End Sub

---

Regards, Hans Vogelaar

Answer 2

Just tried this.  Very nice, Hans!!

---

Ryan Shuell