VBA CODE REQUIERED FOR ADDING BLANK ROW

[Suri]

I need Every 3 rows after one blank row.

 

[Hui]

Suri


Can you please be more specific in detailing your requirements

 

[Debraj]

Hi Suri,


To insert a blank row after every 3rd Row.. use the below Code n VBA..

[pre]

Sub InsertRow()
For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If i Mod 3 = 0 Then
Cells(i, 1).EntireRow.Insert xlShiftDown
End If
Next i
End Sub

[/pre]
PS: Please don't take my suggestion to 'Avoid using VBA if at all it is possible by Excel'

but, you can do ..


add a blank Column at A1..

at A1 write 0.33

at A2 write 0.66

now Fill the Entire Column.. by Drag / Fill Handle Series..

Come to the bottom of the Column..(last Row) for Exp.. A123.

at the next cell (A124) write 1

at A125 write 2

again drag upto some(atleast 124/3) blank ROW..


Now the tricky part.. Select all the cells (Ctrl + A).

Sort..

It will sort > 0.33,0.66,0.99,1,1.32,.... where "1" is a blank Row.. at the Bottom..now comes in between..

Then Delete the the inserted Column..(A)..

If you catch the trick then.. you can add blank ROW on each 2 rows also...


Code Edited...

 

[SirJB7]

@Debraj Roy


Hi!


I think that it'd be useful to add the option for not counting titles in the Sub InsertRow(). Perhaps adding a constant:

Const kiTitles = NN

(where NN>=0), and then changing IF condition to:

If (I - kiTitles) Mod 3 = 0 Then


Regards!

 

[Debraj]

Hi JB-007,


I have used.. ActiveSheet.UsedRange.Rows.Count To 2 only to 'Not Counting Title' by assuming Title is in Row 1..


I thought to used step -3 also, but only due to Number of Post 5, by suri.. I tried to give him.. as simple as possible answer..


Regards,

Deb