Inserting a blank row after a string in Excel

Question

I am trying to create a macro in excel 2010 that finds every cell in a sheet with a value of "All Customers." Every time that value is found I need a blank row inserted below it. Thought it would be pretty simple but I have searched I many forums and tried to use some sample code and I can't get it to work properly. I am a complete newb when it comes to VBA stuff. Thought I would post here and go do some light reading on basics of VBA.

If anyone has any good training resources, please post those as well.

Thanks in advance!

EDIT: In my OP, I neglected to mention that any row that contains a value of "All Customers" would ideally be highlighted and put in bold, increased size font.

These actions are something that an old Crystal Report viewing/formatting program used to handle automatically when pulling the report. After we upgraded the program I learned that this type of formatting ability had been removed with the release of the newer version of the program, according to the software manufacturer's tech support. Had this been defined in the release notes I would have not performed the upgrade. Regardless, that is how I found myself in this macro disaster.

Answer 1

The error is because the worksheet was not specified in used range. I have slightly altered the code with my text being in column AJ and inserting a row above the cell.

Dim R As Integer

For R = ActiveSheet.UsedRange.Rows.Count To 1 Step -1

  If Range("AJ" & R) = "Combo" Then Rows(R).Insert

Next R

Answer 2

Something like this code adpated from an article of mine here is efficient and avoids looping

1. It bolds and increase the font size where the text is found (in the entire row, as Tim points out you should specify whether you meant by cell only)
2. It adds a blank row below the matches

code

Option Explicit

Const strText As String = "All Customers"

Sub ColSearch_DelRows()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim cel1 As Range
    Dim cel2 As Range
    Dim strFirstAddress As String
    Dim lAppCalc As Long
    Dim bParseString As Boolean

    'Get working range from user
    On Error Resume Next
    Set rng1 = Application.InputBox("Please select range to search for " & strText, "User range selection", ActiveSheet.UsedRange.Address(0, 0), , , , , 8)
    On Error GoTo 0
    If rng1 Is Nothing Then Exit Sub

    'Further processing of matches
    bParseString = True

    With Application
        lAppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    'a) match string to entire cell, case insensitive
    'Set cel1 = rng1.Find(strText, , xlValues, xlWhole, xlByRows, , False)

    'b) match string to entire cell, case sensitive
    'Set cel1 = rng1.Find(strText, , xlValues, xlWhole, xlByRows, , True)

    'c)match string to part of cell, case insensititive
     Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , False)

    'd)match string to part of cell, case sensititive
    ' Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , True)

    'A range variable - rng2 - is used to store the range of cells that contain the string being searched for
    If Not cel1 Is Nothing Then
        Set rng2 = cel1
        strFirstAddress = cel1.Address
        Do
            Set cel1 = rng1.FindNext(cel1)
            Set rng2 = Union(rng2.EntireRow, cel1)
        Loop While strFirstAddress <> cel1.Address
    End If

    'Further processing of found range if required
    If bParseString Then
        If Not rng2 Is Nothing Then
        With rng2
        .Font.Bold = True
        .Font.Size = 20
        .Offset(1, 0).EntireRow.Insert
        End With
    End If
    End If

    With Application
        .ScreenUpdating = True
        .Calculation = lAppCalc
    End With

End Sub

Answer 3

This function starts from the last row and goes back up to the first row, inserting an empty row after each cell containing "All Customers" on column A:

Sub InsertRowsBelowAllCustomers()
  Dim R As Integer
  For R = UsedRange.Rows.Count To 1 Step -1
    If Cells(R, 1) = "All Customers" Then Rows(R + 1).Insert
  Next R
End Sub

Answer 4

Assuming this is on the first sheet ("sheet 1"), here is a slow answer:

Sub InsertRowsBelowAllCustomers()

    'Set your worksheet to a variable
    Dim sheetOne as Worksheet 
    Set sheetOne = Worksheets("Sheet1")

    'Find the total number of used rows and columns in the sheet (where "All Customers" could be)
    Dim totalRows, totalCols as Integer
    totalRows = sheetOne.UsedRange.Rows.Count
    totalCols = sheetOne.UsedRange.Columns.Count

    'Loop through all used rows/columns and find your desired "All Customers"
    Dim row, col as Integer
    For row = 1 to totalRows
        For col = 1 to totalCols
            If sheetOne.Cells(row,col).Value = "All Customers" Then
                  Range(sheetOne.Cells(row,col)).Select
                  ActiveCell.Offset(1).EntireRow.Insert
                  totalRows = totalRows + 1 'increment totalRows because you added a new row
                  Exit For  
            End If 
        Next col
    Next row
End Sub 

Answer 5

Public Sub InsertRowAfterCellFound()

    Dim foundRange As Range
    Set foundRange = Cells.Find(What:="yourStringOrVariant", After:=ActiveCell) 'Find the range with the occurance of the required variant

    Rows(foundRange.Row + 1 & ":" & foundRange.Row + 1).Insert 'Insert a new row below the row of the foundRange row

    foundRange.Activate 'Set the found range to be the ActiveCell, this is a quick and easy way of ensuring you aren't repeating find from the top

End Sub

You may need to add error handling to the code as you will get an error if no cell with the specified value is found.