vba to insert row based on criteria

[drewbny]

Hi, how can i create a macro to insert rows based on a name of a cell in a column meeting some criteria?

for example, i have a sheet where i want to insert a row above any row where the cell in column C = North Florida.

also could i create a macro where i insert a row above the row in column c = North Florida and copies the formula from the row below it?

 

[portews]

Try this:

Sub InsertAbove()
'https://www.mrexcel.com/board/threads/vba-to-insert-row-based-on-criteria.1213482/
'inserts row above text you input for found in the C column.
Dim LastRow As Long
Dim SearchFor As String

'find the last row
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

'pop an input box to search for a specific string (North Florida)
SearchFor = InputBox("Input text to insert row above", "Insert Above")

'If SearchFor is empty, you hit Cancel
If SearchFor = "" Then End

'loop through all the rows starting at the bottom
For i = LastRow To 1 Step -1
    'if the string you're looking for is in column C
    If Cells(i, 3).Text = SearchFor Then
        
        'copy the whole row (rem out if you don't want to copy the whole row.
        Rows(i).Copy
        'paste it.  It will carry all info, including formulas from the following row.
        Rows(i).Insert
        
        'if you only want to copy the C column formula from the line below, rem out "Rows(i).Copy" and unrem this next line
        'Cells(i, 3) = Cells(i + 1, 3).Formula
        
    End If
Next
End Sub