Common Spacing of Rows

[rajeev]

Hi,
I have a data that runs to about 7500 rows containing different
components.I want to create a common space say 5 rows after every
change of components.Right now i am doing it manually & it takes a hell
of a time to complete it.Anyone can help me to solve this problem.
Thanks in advance.

Regards
Rajeev

 

[Gord Dibben]

Rajeev

This macro will insert 5 blank rows after each change of component in column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) Then _
Cells(i, 1).Resize(5, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben Excel MVP

 

[Don Guillett]

try this assuming column A

Sub insertrows()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) Then _
Rows(i).Resize(5, 1).EntireRow.Insert
Next i
End Sub

 

[rajeev]

Don,
Very difficult to understand.Will you please clarify it in simple way.

Regards
Rajeev

 

[rajeev]

Dear Gord,
Truely speaking it is very difficult to understand.I am an accountant &
does not have any software skills.Will you please explain it in a
simple way.

Regards
Rajeev

 

[Gord Dibben]

Rajeev

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

 

[rajeev]

Thanks lot Gord at last i have got it but i am
facing a new problem.The macro creates a gap of 5 rows between every
part no.However i want to create a gap only when the part no.
changes.Suppose if 3 rows are occupied by same part no. then i want
that macro should run only after that part no. changes.
However once again thanks lot for solving substantial portion of my
problem.

Rajeev

 

[Don Guillett]

Gord's macro will work, as written, if your part numbers are in col A. If
not, modify to suit.

 

[Gord Dibben]

Rajeev

As written and posted, the macro will insert 5 rows at each change in part
number if the numbers are contiguous in column A.

If it is inserting 5 rows between every part number then your part numbers are
not the same or not contiguous or not in Column A.

In column A

1
1
1
2
2
2
3
3
3

The macro will insert 5 rows after A3 and A6


Gord

 

[rajeev]

Thanks Gord i now got it.
Ealiar it takes me at least 10hrs to get the work completed.Now few
seconds.