CODEWITHSUNDEEP
excelvba
Andrew Barsness
Andrew Barsness

Reputation: 11

How to add a row when a certain value in a cell is selected?

I have a set of data:

Data list

I have data validation set up for entering Y or N within the "more issues Column".

I need a row to be inserted under that when Y is selected, without having to run a macro.

I am running Excel 2016.

Upvotes: 0

Views: 439

Answers (2)

GMalc
GMalc

Reputation: 2628

Try this macro: place in the worksheet object. *Updated

Private Sub Worksheet_Change(ByVal Target As Range)
    'If the cell already has a capital "Y" and you 
    'double click the cell it will insert a row.
    'And typing a "Y" into any other column will not error 

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Columns(2)) Is Nothing Then
        If Target.Value = "Y" Then
            Target.Offset(1).EntireRow.Insert Shift:=xlDown
        End If
    End If

End Sub

Upvotes: 1

Tony M
Tony M

Reputation: 1762

Here's some working code to get you started. Just place the code in the sheet you are using by right clicking the sheet tab and selecting view code. 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
If Not Application.Intersect(Range("B2:B8"), Range(Target.Address)) Is Nothing Then
    Set r = Target
    If r = "Y" Then r.Offset(1, 0) = "populated cell"
End If
End Sub

The animated gif (click to see detail) show entering N or Y and having the cell below only Y's populated. Ask if you have questions about how this works.

enter image description here

Upvotes: 0

Related Questions