CODEWITHSUNDEEP
vbaexcel
ChandraSekhar Reddy
ChandraSekhar Reddy

Reputation: 11

macro to insert blank rows based on column value

I'm unsuccessfully trying to create a macro to insert blank row based on cell value.

I have a bulk data, in which one column has differing numbers. Based on the column value, I need to insert a blank row below it.

Upvotes: 1

Views: 14470

Answers (2)

aLearningLady
aLearningLady

Reputation: 2088

The below is an example for if you are looking to insert a blank row based on a sudden change of value in a column (in this case column "C"): 

Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 3 Step -1
    If Cells(lRow, "C") <> Cells(lRow - 1, "C") Then Rows(lRow).EntireRow.Insert
Next lRow

You can change Cells(lRow - 1, "C") to whatever value you want to trigger your row insert and, of course, what column this is being applied to.

Upvotes: 2

Richard
Richard

Reputation: 105

If I understand you properly this should do what you want.
Just change "A:A" to the range your working with and the If cell.Value = 1 Then to the condition you need to find the cell you want to add a blank row under.

Dim i As Range
Dim cell As Range
Set i = Range("A:A")
For Each cell In i.Cells
If cell.Value = 1 Then
cell.Offset(1).EntireRow.Insert
End If
Next

Upvotes: 1

Related Questions