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

[Guest]

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

 

[Don Guillett]

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

 

[Mike Fogleman]

A formula will not do this.

Mike F

 

[Guest]

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

 

[Don Guillett]

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.

 

[Guest]

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

 

[Guest]

Hi Don,

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

Thanks again

-Pman

 

[Don Guillett]

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

 

[Guest]

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

 

[Don Guillett]

???

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

Thanks again Don

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)