| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I insert a row in MS Excel using a formula?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
A formula will not do this.
Mike F "Pman" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
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 "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
Hi Don,
Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... Hi Don, Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... Hi Don, Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
???
-- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... Hi Don, Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
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" wrote: ??? -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... Hi Don, Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
one line for a-g
cells(i-1,"a").resize(,6).etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: ??? -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... Hi Don, Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I insert a row in MS Excel using a formula?
Thanks again Don :)
"Don Guillett" wrote: one line for a-g cells(i-1,"a").resize(,6).etc -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: ??? -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... Hi Don, Is it possible to insert a line/ border whenever I insert the row? Thanks again :) -Pman "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Pman" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to insert time of day in excel cell | Excel Worksheet Functions | |||
| formula to insert time of day in excel cell | Excel Discussion (Misc queries) | |||
| formula to insert time of day in excel cell | Excel Worksheet Functions | |||
| insert logical formula in excel worksheet | Excel Worksheet Functions | |||
| How do I set up a formula to insert text in excel? | Excel Discussion (Misc queries) | |||
Linear Mode
