Insert Line (Shift Cell down)

[sadasivan]

Hi All
Let me know how to insert Shift Cell down every 5th row

 

[Somendra Misra]

Hi,

Try below code:

Sub insertRow()

Dim lr As Long
Dim i As Long

With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
End With
cntr = 0
For i = lr To 6 Step -1

    cntr = cntr + 1
    ans = cntr Mod 5
    If ans = 0 Then
  
    Range("A" & i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
    Next i
End Sub

Regards,

 

[Ananda Kumar C]

Hi Sadasivan,

Alternatively you can enter this formula in cell B2 and copy it down until B36

=IF(MOD(ROW(A2)-ROW(A$2)+1,6)=0,"",OFFSET(A$2,MOD(ROW(A2)-ROW(A$2)+1,6)+QUOTIENT(ROW(A2)-ROW(A$2)+1,6)*5-1,0))

I have given the generic formula assuming data start cell can vary. Here data start cell is A2 and I have used A$2 in the formula for the same. If this is fixed, you can further simplify the formula and just say this:

=IF(MOD(ROW(A2)-1,6)=0,"",OFFSET(A$2,MOD(ROW(A2)-1,6)+QUOTIENT(ROW(A2)-1,6)*5-1,0))

Anand

 

[Debraj]

Here is another tricks..

PS: in case of exclude 1st row..
In Advance Filter List range section.. start from Row 2..

 

[sadasivan]

Hi Sadasivan,

Alternatively you can enter this formula in cell B2 and copy it down until B36

=IF(MOD(ROW(A2)-ROW(A$2)+1,6)=0,"",OFFSET(A$2,MOD(ROW(A2)-ROW(A$2)+1,6)+QUOTIENT(ROW(A2)-ROW(A$2)+1,6)*5-1,0))

I have given the generic formula assuming data start cell can vary. Here data start cell is A2 and I have used A$2 in the formula for the same. If this is fixed, you can further simplify the formula and just say this:

=IF(MOD(ROW(A2)-1,6)=0,"",OFFSET(A$2,MOD(ROW(A2)-1,6)+QUOTIENT(ROW(A2)-1,6)*5-1,0))

Anand

Thank you sir.

 

[sadasivan]

Here is another tricks..

PS: in case of exclude 1st row..
In Advance Filter List range section.. start from Row 2..

Thank you sir.