macro to Insert rows at every change in a column

[karthikr22]

hi,

I would like to know the Macro code for Inserting rows at every change
in a given column

Regards

Macro man

 

[Bernie Deitrick]

MMan,

Try the macro below, based on column D. To change that, change the line

Set myRange = Range("D")

to reflect your actual desired column.

--
HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRow As Long
Dim StrtRow As Long
Dim myCol As Integer
Dim myRange As Range

Set myRange = Range("D")

myCol = myRange.Column
StrtRow = myRange(65536).End(xlUp).Row

For myRow = StrtRow To 2 Step -1
If Cells(myRow, myCol).Value <> Cells(myRow - 1, myCol).Value Then
Cells(myRow, myCol).EntireRow.Insert
End If
Next myRow
End Sub

 

[karthikr22]

Hi Bernie,

Thanks a lot for giving me this code. its great!!!!!

 

[karthikr22]

hi Bernie,

Can you pls give me the code for defining the range through Input box
for the code which you gave for "macro to Insert rows at every change
in a column".

Thanks

Macro Man

 

[Bernie Deitrick]

Macro Man,

Set myRange = Application.InputBox(Prompt:="Select your range", Type:=8)

HTH,
Bernie
MS Excel MVP