Solved Need excel insert rows when a cell receives specific value

January 4, 2017 at 03:45:13
Specs: Windows 7
Hi,
I've got this column that is being filled by users with one of these options: A, AN or RR. If RR is selected, I want Excel to automatically add a row below ( the rows are inclusive of 36 columns. When the row is inserted, I need it to insert some user inserted cell values of the above row to itself.
I'm no programmer but familiar with formulas. I know the insert cannot be done through formulas...
Please help.
Thanks,
Amo

See More: Need excel insert rows when a cell receives specific value

Report •

#1
January 4, 2017 at 05:53:45
✔ Best Answer
Without specifics related which Column will contain RR and what data you want retained, the best we can offer is an example of how it could be done.

The following code assume that the RR will be entered in Column C. It also assumes that you want to retain all values except for those in Columns B, D, F & H.

The code copies/inserts the entire row and then Deletes the values that aren't needed. There may be a more efficient way to meet your requirements, but without specifics, that's the best we offer.


Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if RR was entered into Column C
   If Target.Cells.Count = 1 And Target.Column = 3 Then
    If Target = "RR" Then
'Copy/insert Row and Delete unneeded values from new Row
       Application.EnableEvents = False
         Target.EntireRow.Copy
         Target.EntireRow.Insert shift:=xlDown
          clrCell = ActiveCell.Row
          Application.Union(Cells(clrCell, 2), Cells(clrCell, 4), _
                            Cells(clrCell, 6), Cells(clrCell, 8)).ClearContents
       Application.EnableEvents = True
    End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question