Need extra sheet Rows

[texasblue4]

Hi...I have 675 numbered rows in ascending order...I need to add an additional 4 blank rows below each numbered row..How do I get the additional rows inserted besides inserting manually below each numbered row?

Thank you for your help.

[TMS]

VBA.


Regards, TMS

[texasblue4]

I'm sorry I don't know what VBA is or how to "use" it in conjunction with Excel?

Thank you for replying to me.

Kind Regards,
Sherry

VBA.


Regards, TMS

[Pete_UK]

Copy those numbers (and the heading if you have one) into a blank column somewhere (e.g. into column M). Then select the block of numbers and click on <copy>. Then press <End> once followed by <arrow-down), then <arrow-down) once and CTRL-V to paste the numbers below the first set. Repeat three more times, so you have 4 copies of the numbers below the first set.

Then select the whole of your data and the set of numbers (down to row 3375 or thereabouts), and then sort using column M as the sort field. Then you can delete column M.

Hope this helps.

Pete

[TMS]

@Pete: clever

[FDibbins]

Dont think that is what the OP wants that will give them 4 sets of their numbers, but I think that want to insert 4 rows between their numbers, ie 1...1a...1b...1c...1d 2...2a...2b...2c...2d etc

[TMS]

@Ford. No, I don't think so. The numbers are replicated and sorted in another column, say M. So it will bring the numbers together but column A will have blanks inserted ... though I haven't tried it. Looks right. Sure Pet will have tested it.

[Pete_UK]

Ford,

I did a little test, starting with this:

num … test
1 … 1
2 … 2
3 … 3
4 … 4
… 1
… 2
… 3
… 4
… 1
… 2
… 3
… 4
… 1
… 2
… 3
… 4
… 1
… 2
… 3
… 4

When I sorted it on the test column I got this:

num … test
1 … 1
… 1
… 1
… 1
… 1
2 … 2
… 2
… 2
… 2
… 2
3 … 3
… 3
… 3
… 3
… 3
4 … 4
… 4
… 4
… 4
… 4

which is what I thought the OP wanted.

Pete

EDIT: column A is empty apart from the numbers and the heading, though it doesn't show very well.

Pete

[Pete_UK]

Thanks for the rep, Trev - just a few more to go to the 4,000 milestone !!

Pete

[TMS]

@Pete: you're welcome. Simple, neat, and beats the hell out of my suggestion

[Pete_UK]

Well, I was doing that 20+ years ago on Quattro Pro, so it's hardly new !!

Still, if I can pass on a tip to someone ...

Pete

[FDibbins]

thats a blast from the past, Pete. I cut my teeth on QPro

Good 1, seems I did not follow it all the way through

[Pete_UK]

Thanks for the Rep, Ford - broken through that 4k barrier now.

Pete

[TMS]

That's 42% rep to posts, give or take Better than mine at 39%

[Pete_UK]

Well, it's you kind mega-posters who have helped to boost it ...

I used to reckon on about 33%, but just recently (last few months) I seem to have helped quite a few posters with start dates in 2005 or a few years later, and their rep power is quite high even if they don't have a high post count.

Pete

[newdoverman]

Here is one way of getting the values to be separated with 4 empty cells.
Assuming the original values are in column A from A1:A675. Enter in B1 the following formula and fill down until blank cells.

Please Login or Register  to view this content.

In C2 enter this formula and fill down:

Please Login or Register  to view this content.

Select column C and copy then Paste Values.

This is how your data will look (this is just the very top of the data)

	A	B	C
1	1	1
2	2	1
3	3	1
4	4	1
5	5	1
6	6	2	1
7	7	2
8	8	2
9	9	2
10	10	2
11	11	3	2
12	12	3
13	13	3
14	14	3
15	15	3
16	16	4	3

[newdoverman]

You could also use this in column B2 and fill down until blank cells result then select column B, COPY, PASTE SPECIAL, VALUES.

Please Login or Register  to view this content.

RESULT:

	A	B
1	1
2	2	1
3	3
4	4
5	5
6	6
7	7	2
8	8
9	9
10	10
11	11
12	12	3
13	13
14	14
15	15
16	16
17	17	4

[Pete_UK]

I assumed that the OP had data in other columns, which is why I suggested using column M for the repeating number sets. If that is the case, then a formula solution becomes a bit messy, and you have to fix the values afterwards and get rid of the original data.

Pete

[newdoverman]

Thanks Pete.

If there is data along the rows then this could be done.
Assuming data on Sheet1 starting in A2.
On Sheet2!A2 enter this formula and fill down until blanks. This will give the numbers separated by 4 blanks.

Please Login or Register  to view this content.

In Sheet2!B2 enter this formula and fill down and across as needed. This will copy the data leaving 4 blank rows between data.

Please Login or Register  to view this content.

Select all the data on Sheet2 and copy, paste values to get rid of the formulae. Delete Sheet1.

[TMS]

Delete Sheet1.

maybe not, I think

[newdoverman]

If ALL the data has been copied over, formulae eliminated in sheet2 referring to sheet1, then sheet1 becomes redundant unless there is something not being said here.

[Pete_UK]

Perhaps File | Save As to save the file with a different name, so that the original data is retained if required.

Pete

[TMS]

You seem to be making the assumption that there are no formulae in Sheet1, or that it is acceptable to convert them to values.

There's probably a lot not being said. No input from the OP for some time.

[newdoverman]

When making changes to worksheets/workbooks, I always work on a copy and never the original. You just never know what all the changes will do and accidents do occur.

[TMS]

OK, I'm officially confused now. Let me talk this through for my own benefit. Please correct me when I go wrong. The original data is in Sheet 1. It may, or it may not have formulae, formatting, Conditional Formatting or Data Validation.

In Sheet2, in cell A2, you put a formula and copy it down. Presumably, this will create the blank cells between the populated cells. You then put another formula into Sheet2, cell B2 and copy it across and down. This will provide references to Sheet1 and cells will be blank where there is no matching data (that is, for the blank cells in column A).

At that point, you have the original data with blank rows inserted ... and you copy all the data and Paste Special | Values to remove the references to Sheet1.

So far, so good?

And then you delete Sheet1, with the original data, formulae, formatting, Conditional Formatting and Data Validation, if present.

Select all the data on Sheet2 and copy, paste values to get rid of the formulae. Delete Sheet1.

That sounds like an accident. Have I misunderstood what you are suggesting?


Regards, TMS

[newdoverman]

The original specification requested the insertion of 4 blank rows between rows of data. There is no mention of formatting, formulae or anything else.

What I offered, meets the condition of the original request. The rest is speculation by everyone else and the originator or the thread has made no attempt at clarification.

[TMS]

Fair comment. We shall wait and see

[newdoverman]

I like Pete's solution as it is quick, easy and no formulae to deal with and problems with formatting, Data Validation and formulae are likely easily dealt with if necessary.

[TMS]

Me too. Can't really go wrong with it.

[Pete_UK]

Another problem with using formulae on a new sheet, then fixing values and removing Sheet1, is that there might be other sheets in the original file which reference data on Sheet1 - if you delete that sheet then the references in the dependent formulae are lost.

But, as Ron says, this is all speculation, so let us hope that the OP gets back to us sometime soon.

Pete