Inserting blank row when data is entered in specific cell.

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings, I have data entered into a workbook beginning on A16. Row 15 is a Header row. Once the user selects an item from the drop box J16 I would like a new line inserted for A16. I don't want the line to use Row 15's format. I made an attempt with the VBA below, but nothing seemed to happen. There are about 5 choices to choose from in J16 which is why I used <>. Thank you.

VBA Code:
Sub InsertBlankRowsBasedOnCellValue()
    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long

        Col = "J"
        StartRow = 16
        BlankRows = 1

            LastRow = Cells(Rows.Count, Col).End(xlUp).Row

            Application.ScreenUpdating = False

            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) <> "" Then
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
It looks like you have a drop down not just in cell J16 but down the column and you want to insert a blank row below each row where column J is not blank. Is this correct? The macro you posted would add a blank row each time you run the macro resulting in multiple blank rows. I don't think that is what you want. You want only one blank row inserted when a selection is made in column J. Is this correct?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,206
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top