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

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

On 8 May 2005 06:04:29 -0700, "rajeev" wrote:

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

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


--
Don Guillett
SalesAid Software

"rajeev" wrote in message
ups.com...
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

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

Regards
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

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

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


Gord Dibben Excel MVP

On 9 May 2005 01:55:21 -0700, "rajeev" wrote:

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

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

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

--
Don Guillett
SalesAid Software

"rajeev" wrote in message
oups.com...
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

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


On 10 May 2005 00:31:16 -0700, "rajeev" wrote:

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

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