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

[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


--
Don Guillett
SalesAid Software
[email protected]
"rajeev" <[email protected]> wrote in message
news:[email protected]...
> 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

On 8 May 2005 06:04:29 -0700, "rajeev" <[email protected]> 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

[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

On 9 May 2005 01:55:21 -0700, "rajeev" <[email protected]> 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

[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

[foxxkat]

hi Gord, nice one. saves me tons of time.

which field (in your VB code) should i modify if i want the macro to work based on other columns?

for example Column A (country) is all Japan,
column B (cities) i have Tokyo, Osaka.. etc and when the row changes (Toyko to osaka or other cities) then the macro add 5 rows to it..

one way i can think of (without modifying your VB code) is to shift my columns then run the macro, after that shift columns back.

2nd question, how should i add to the code to make it add 5 rows AND THEN copy the row on top?


thanks,

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"rajeev" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[foxxkat]

hmm.. cant see which to modify.. do i change the "i"?

keep on trying..

[swatsp0p]

Foxxkat: your posts are NOT being seen by Gord (or anyone outside of this forum for that matter). That function in this forum is not working. Posts entered here are not sent up to the newsgroups.

Sorry.

[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


On 10 May 2005 00:31:16 -0700, "rajeev" <[email protected]> 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

[rajeev]

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