Insert a new row after a certain number of rows

[tialongz]

Hi Everyone,

I am working with Excel 2003.

I want to insert a new row after a certain number of rows. For example, I want to insert a new row every 30 rows, and also insert a new row after every 45 rows. Anyone know how to do this?

I also want to for example, bolded and underline (modify the format) every 10 rows in a specific column(s) or cell rather.

Is it the same with indenting certain cells?
Thanks in Advance!

[Ken Puls]

Sorry, don't quite follow...

You want to insert a row at 30, 45, 60, 105, etc...? OR 30, 75, 105, 150... Or...?

Can you give the sequence of the rows?

[tialongz]

Sorry, don't quite follow...

You want to insert a row at 30, 45, 60, 105, etc...? OR 30, 75, 105, 150... Or...?

Can you give the sequence of the rows?

Let's say for every customer I have on my list, takes 39 rows. My data starts at row 4, I need to insert a new row at row: 13, 23, 28, 36, 40, and 43 (these new rows are inserted for 1st customer and the pattern repeats itself). 2nd customer, new rows would be at row 44+13, 44+23, ... 44+43=77.

Hope this is what you are asking for.

Thanks for the help.

[austenr]

I think a visual aid is needed here. Could you post a sample?

[Bob Phillips]

I may be thick, but 30 and 45 looks slightly different to 13, 23, 28, 36, 40, and 43 to me.

[Ken Puls]

I may be thick, but 30 and 45 looks slightly different to 13, 23, 28, 36, 40, and 43 to me.

Maybe a new style of math that we're not aware of? :dunno

[tialongz]

I think a visual aid is needed here. Could you post a sample?

I cannot upload at the moment, but if you could go to this link, I posted a sample, how the excel file looks like.

I'll post the link on the next post. I didn't know I need to post 5 posts, to be able to include a link.

It has like 4 screen shots, i think the third one is the excel file, I need to sum all the products for each of the business. But there is a need to insert a new row so I could do autosum function. This is just a dummy data, on the actual one, all the customers will have the same amount of rows, and same businesses, and products.

Hope this clarify a tad
thanks in advance

[tialongz]

Here is the link:
http://www.dbforums.com/showthread.php?t=1612438 (http://www.dbforums.com/showthread.php?t=1612438) (go to the last post)

[tialongz]

Maybe a new style of math that we're not aware of? :dunno

:whistle: I hope this does not implicate things.

But all customers have the exactly the same 39 rows. It is basically copying and pasting the same 39 rows over and over again, just under different customer names.

[shades]

So, this means that you could search for the name and then insert the 39 rows one row below the name that SEARCH/FIND provides?

[tialongz]

So, this means that you could search for the name and then insert the 39 rows one row below the name that SEARCH/FIND provides?

I guess in a way, if I am understanding you correctly.

You seen my screenshots, if you just ignore the first company, and imagin the format of the second company repeated over and over again, but under different names. Like there is no extra row between businesses, so I couldn't use the autosum function to get the sums for each of businesses for each company.

[mdmackillop]

try
Option Explicit

Sub AddRows()
Dim LRw As Long
Dim rng As Range
Dim i As Long

Application.ScreenUpdating = False
LRw = Cells(Rows.Count, 1).End(xlUp).Row - 38
For i = LRw To 4 Step -39
Set rng = Union(Cells(i + 9, 1), Cells(i + 18, 1), Cells(i + 22, 1), _
Cells(i + 29, 1), Cells(i + 32, 1), Cells(i + 34, 1), Cells(i + 39, 1))
rng.EntireRow.Insert
Set rng = Nothing
Next
Application.ScreenUpdating = True
End Sub

[tialongz]

try
Option Explicit

Sub AddRows()
Dim LRw As Long
Dim rng As Range
Dim i As Long

Application.ScreenUpdating = False
LRw = Cells(Rows.Count, 1).End(xlUp).Row - 38
For i = LRw To 4 Step -39
Set rng = Union(Cells(i + 9, 1), Cells(i + 18, 1), Cells(i + 22, 1), _
Cells(i + 29, 1), Cells(i + 32, 1), Cells(i + 39, 1))
rng.EntireRow.Insert
Set rng = Nothing
Next
Application.ScreenUpdating = True
End Sub


I tried it. But the new rows are inserted at the wrong places. My first row (or first customer) starts at row4, 2nd starts at row 43, 82 and so on.

For my first customer, I need to insert new rows at row 13, 23, 28, 36,40 and 43. For my second customers, new rows need to be inserted at 13+39, 23 +39.....43+39.

If you could, please explain the code, so maybe I could try to modify it or..test it. I tried to change those numbers in the brackets, still no luck.

Not sure if this helpful, but there might be a empty space at row 13, 23, or 28. Because other columns at row 13, 23 or 28 might have data in them, I am trying to insert a row so.. all columns are empty.

Thanks in advance.

[austenr]

Did you ever read a thread and end up saying "What?" :dunno That is how I feel reading this.

I read the link the OP provided. I think that the data he has either needs to be delt with totally in Access (which is where the report in the link is generated), or import the raw data to a workbook (Data>Import External Data) and proceed from there. Something is getting lost in the translation, IMHO.

Apparently there is a learning curve to deal with as well. IMHO.

[mdmackillop]

The sample I supplied will insert new rows numbered 13, 23, 28, 36,40 and 43, and also between Customers. FYI, because rows are being inserted, new rows are inserted in the last Customer first. If you want the new lines in different positions, just change the + numbers in the code to suit.
If this is not what you are after, please make up a simple sample showing exactly what you want and post it using Manage Attachments in the Go Advanced section. Your link made no sense to me.

[austenr]

Ok let me give this a shot. Starting at row 4 which is the first row of your first customer, you want a blank row inserted after these rows 13, 23, 28, 36,40 and 43. Row 43 would be a blank row immediatly under the last row of data for customer 1.

Then beginning at row 44, (the first row of data for customer 2), count 9 rows and insert a blank row, 10 more rows and insert a row, 5 rows and insert a row, 8 rows and insert a blank row, 4 rows then a blank row and three rows and insert a blank row.

The cycle then repeats for the third customer?

Is that even close to being what you are after?

And the obvious question (well maybe not, what will the blank row be used for?) If its for totaling then there is a far easier way to do what you want.

[tialongz]

Ok let me give this a shot. Starting at row 4 which is the first row of your first customer, you want a blank row inserted after these rows 13, 23, 28, 36,40 and 43. Row 43 would be a blank row immediatly under the last row of data for customer 1.

Then beginning at row 44, (the first row of data for customer 2), count 9 rows and insert a blank row, 10 more rows and insert a row, 5 rows and insert a row, 8 rows and insert a blank row, 4 rows then a blank row and three rows and insert a blank row.

The cycle then repeats for the third customer?

Is that even close to being what you are after?

And the obvious question (well maybe not, what will the blank row be used for?) If its for totaling then there is a far easier way to do what you want.
Yes, I know. I will try to clarify a bit.
To answer you obvious question, yes I am inserting the new rows so I could do the subtotals. (I need the totals for the products for each of the businesses.)

I tried to use the subtotal function and applies to the entire sheet, but the total rows are showing up at the wrong places.

I have attached an excel file, I want subtotals after the last product of each business.

Thanks in advance.

[ndendrinos]

Hello tialongz,
I've added to the last sample you give ... maybe this can help?
I

[mdmackillop]

I have attached an excel file, I want subtotals after the last product of each business.
Why does your sample not relate to the descriptions in your questions? I still don't know where the "wrong" positions are.

[austenr]

Is this what you want?

[tialongz]

Why does your sample not relate to the descriptions in your questions? I still don't know where the "wrong" positions are.

A:
My initial question was based on the 'actual' data I am working on. The sample I provided is 'dummy data', the design is the same as the actual. Just the number of rows are different.

Wrong position:
"I still don't know where the wrong positions are." Are you asking where the wrong positions are...when I used the macro or the subtotal function?

I'll answer both:
I used the code that was given to me from previous posts. That code didn't work, rows are added at the wrong places.

Subtotals: I tried to use the subtotal function. Data -> subtotal. [At each change in] I pick the level I want (products or product column). Then I pick the sum function. [Add subtotals to] I pick the columns I want to add the subtotals to, which are the year columns.

When I did all that, instead of showing the subtotals of all the products, it basically inserted subtotals for every product.

If you still cannot get a clear picture, I am trying to sum up or subtotal all the products under every business for the years 2010, 2011 - if i recall correctly those are the year columns on the sample provided. So, basically, I want an aggregated or sum number under column 2010 and 2011 for all the products under each businesses.

Thanks.

[austenr]

So the solution I provided in my post right above your last one is not what you want? :dunno

Also, if you are providing data for us to test with, even if it is dummy data, please make it in the same format as the real data (number of rows). This gets frustrating to everyone trying to help you. If a solution is provided but the data supplied is not in the same format then everyone trying to help you is wasting their time trying to provide an answer.

[tialongz]

So the solution I provided in my post right above your last one is not what you want? :dunno

Also, if you are providing data for us to test with, even if it is dummy data, please make it in the same format as the real data (number of rows). This gets frustrating to everyone trying to help you. If a solution is provided but the data supplied is not in the same format then everyone trying to help you is wasting their time trying to provide an answer.

Oh I didn't see your post, we were probably writing at the same time. I will check it out tonight. Anyhow, I will try to duplicate the format exactly sometimes tonight. Sorry for the inconvenience.