How can I insert a row in MS Excel using a formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pman - 18 Sep 2007 22:04 GMT

Hi,

I have an excel file which goes like this in my first 2 columns:

PRODUCT    PUBLICATION
NAME

Product 1 publication 1
publication 1
publication 2
Product 2 publication 1
publication 1
publication 2
Product 3 publication 3
Product 4 publication 2

This file runs in 20k + rows.....and I need to insert a new row whenever I
encounter a new product or publication. The end result as I want is below:

PRODUCT    PUBLICATION
NAME

Product 1 publication 1
publication 1

publication 2

Product 2 publication 1
publication 1

publication 2

Product 3 publication 3

Product 4 publication 2

Notice that there's a row between new products and new publications. Can
someone tell me an easier way to do it , or than doing it manually for
20,000+ rows?

Thanks :)

P

Reply to this Message

Don Guillett - 18 Sep 2007 23:22 GMT

copy this macro into a module.

Sub insertspaces()
For i = Cells(Rows.Count, "b").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "b") <> Cells(i, "b") Then Rows(i).Insert
Next i
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 39 lines]
>
> P

Reply to this Message

Pman - 19 Sep 2007 15:26 GMT

Thanks a lot Don :)

Also if I want to repeat it for other columns, I just have to run the macro
again but replace the "b" in the code with the corresponding column name
right?

Thanks again, You made my day :)

-Pman

> copy this macro into a module.
>
[quoted text clipped - 47 lines]
> >
> > P

Reply to this Message

Don Guillett - 19 Sep 2007 15:47 GMT

Yes. Or you could have an input box asking the user which column.
And, if you want to run for multiple columns that can be done to make one
mouse click.
But, are you sure you want to do that cuz you might get rows inserted that
you don't want.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Thanks a lot Don :)
>
[quoted text clipped - 61 lines]
>> >
>> > P

Reply to this Message

Pman - 19 Sep 2007 16:08 GMT

Hi Don,

I just want to insert rows for data in column A and B. I tried out the macro
you gave me and modified it to run for column A as well, however it gave me
addition rows that I didn't need (as you said might happen in your last
reply). Is there a way to do this, since further formatting of the data
depends on this step?

Thanks again Don,
P

> Yes. Or you could have an input box asking the user which column.
> And, if you want to run for multiple columns that can be done to make one
[quoted text clipped - 67 lines]
> >> >
> >> > P

Reply to this Message

Pman - 20 Sep 2007 15:06 GMT

Hi Don,

Is it possible to insert a line/ border whenever I insert the row?

Thanks again :)

-Pman

> copy this macro into a module.
>
[quoted text clipped - 47 lines]
> >
> > P

Reply to this Message

Don Guillett - 20 Sep 2007 18:42 GMT

Sub insertspacesandline()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "d") <> Cells(i, "d") Then
Rows(i).Insert
Cells(i - 1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
Next i
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi Don,
>
[quoted text clipped - 58 lines]
>> >
>> > P

Reply to this Message

Pman - 21 Sep 2007 15:18 GMT

Thanks again Don :)

And to extend the border to a range, I just copy-paste the code " Cells(i -
1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous" multiple times and
change the cells reference right? Or let me just try it out :)

Thanks again, and Have a great weekend Don :)

> Sub insertspacesandline()
> For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
[quoted text clipped - 67 lines]
> >> >
> >> > P

Reply to this Message

Don Guillett - 21 Sep 2007 16:00 GMT

???

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Thanks again Don :)
>
[quoted text clipped - 77 lines]
>> >> >
>> >> > P

Reply to this Message

Pman - 21 Sep 2007 16:24 GMT

Sorry for being so ambiguous Don. What I meant was that with the insert line
command I was getting a line only under the data in Column D.....so if I
wanted them under cells A through G, all I did was copied that line and now
the macro looks as follows:

Sub insertspacesandline()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "d") <> Cells(i, "d") Then
Rows(i).Insert
Cells(i - 1, "d").Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i - 1, "a").Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i - 1, "b").Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i - 1, "c").Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i - 1, "e").Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i - 1, "f").Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(i - 1, "g").Borders(xlEdgeBottom).LineStyle = xlContinuous
End If
Next i
End Sub

So I got a line under the data for the Range A to G.

Thanks.......it worked wonderfully :)

Btw is there a rating system where I could rate your help?? I'd marked you
the highest possible points for helpgulness and prompt answers :)

Thanks again :)

> ???
>
[quoted text clipped - 79 lines]
> >> >> >
> >> >> > P

Reply to this Message

Don Guillett - 21 Sep 2007 20:54 GMT

one line for a-g
cells(i-1,"a").resize(,6).etc

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Sorry for being so ambiguous Don. What I meant was that with the insert
> line
[quoted text clipped - 114 lines]
>> >> >> >
>> >> >> > P

Reply to this Message

Pman - 24 Sep 2007 14:28 GMT

Thanks again Don :)

> one line for a-g
> cells(i-1,"a").resize(,6).etc
[quoted text clipped - 117 lines]
> >> >> >> >
> >> >> >> > P

Reply to this Message

Mike Fogleman - 18 Sep 2007 23:54 GMT

A formula will not do this.

Mike F

> Hi,
>
[quoted text clipped - 39 lines]
>
> P

Reply to this Message