adding multiple rows 2

[mscallisto]

Is there a quick way to insert a row between each row in a selected range (not using VBA)

example:

aaa
bbb
ccc

aaa

bbb

ccc

 

[CFNB]

I would insert the first one as usual then CTR-Y after that (it would re-do the last command)

 

[mscallisto]

Thanks CFNB but I would have to re-position before each ctrl-Y

 

[AnotherHiggins]

Why would you need to do this?

The best way I can think of is a quick macro. Something like

Sub InsertRows()
RowCount = [A63356].End(xlUp).Row
For i = RowCount To 2 Step -1
    Range("A" & i).EntireRow.Insert
Next i
End Sub

(Assumes you have a header row - if not, change "RowCount To 2" to "RowCount To 1")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mscallisto]

Why would you need to do this?

Good Question John.

Well I was using excel not as a spreadsheet but as a checklist much like:

eat
drink
be merry
sleep
etc

and simply wanted to "double space" by inserting rows.

Why I used excel instead of Word, who knows.

Yes I could use VBA.

I know that if one highlights every other row then
Insert > Row
excel will insert a row before each highlited row.

I thought there might be a tricky way (non vba) to insert a row between each row.

 

[SkipVought]

"Why?"

Inserting empty rows/Columns in a table DESTROYS the capabilty of many Excel Table processing features, like SORT, FILTER, PicotTable, Chart -- UNLESS you do "heroic" things.

In my book, this is a BIG, no, its a HUGE no, no!!!!!!

Use the ROW HEIGHT or FORMATTING to emphasize "breaks" in the data arrangement.

Skip,

_[red][/red]

 

[AnotherHiggins]

There may well be a slick way. I was thinking about Edit > GoTo, but don't know what you'd do from there.

This isn't very elegant, but it would work....

Assuming you have 50 itmes:
[ul]
[li]In an adjacent column, let's say B, fill down numbers 1, 2, 3, ...50[/li]
[li]Select B1:B50[/li]
[li]Copy[/li]
[li]Go to B51[/li]
[li]Paste[/li]
[li]Sort on column B[/li]
[li]Delete Column B[/li]
[/ul]

Kinda feels like cheating, but, like I said, it will work and is a non-VBA answer.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlhelp]

Posting only because it's there. I like John's version better.

Save your worksheet as a CSV file. Close XL. Open file in Word. Edit, Replace: ^p with ^p^p. Save file. Close Word. Open file in Excel.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

here's yet another alternative.

COPY the list (that has NO EMPTY ROWS).

Scroll to the bottom of the list and PASTE in the next empty row.

SORT the list. (Now there's TWO of each)

Format>Conditional Format - to shade the FONT of repeated rows the same as the INTERIOR shade (usually WHITE).

This give the APPEARANCE of skipped rows, without the annoyance of empty rows.

Skip,

_[red][/red]

 

[xlhelp]

This gives the APPEARANCE of skipped rows, without the annoyance of empty rows.

Yabut, if you were calculating bonuses, I would get double the bonus....yipeeee......

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

I can deal with what I know.

Skip,

_[red][/red]

 

[xlhelp]

Sorry, Skip. I am in a flippant mood today. Primarily because after tomorrow I am going on a long hiatus from Tek-Tips. My job is changing. I may pop-in on a rare ocassion to ask a question or something. Then again, you never know.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

Stand
I

;-)

I am OFTEN in a similar state, Texas not withstanding.

We will miss you. Dew Drop Inn.

Skip,

_[red][/red]

 

[yogia]

Hi mscallisto:

I agree with Skip that instead of inserting a blank row for each item, it would be better to increase the row height for each item. This will keep the EXCEL List or DataBase proper.

If you do want to inser blank rows, you have now received plenty of suggestions, and following is one more way ...

Let us say your list is in column A, then in column B, number those items by keying-in 1,2,3,4, ... and then at the end of this numbering add 1.1,2.1,3.1,4.1, ...
[code]
eat	   1
drink	 2
bemerry   3
sleep	 4
	      1.1
	      2.1
	      3.1
	      4.1

Then I will sort this in Ascending Order on Column B to get ...

eat	   1
	      1.1
drink	 2
	      2.1
bemerry   3
	      3.1
sleep	 4
	      4.1

Now I can optionall delete column B and have a clean list with a blank row for each item in between.



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[mscallisto]

Well Thanks to all for the assistance and many ways to "skin the cat"

And Good Luck to xlhelp; I'm certain many members will miss your expertise; I will.

I guess Skip will have to double up on his responses.

I have over the years inserted rows many times and never thought I had any trouble "sorting" "filtering" etc.

What is it about inserting rows/columns that can destroy table processing features?

 

[SkipVought]

You do not have CONTIGUOUS data. A Table is contiguous.

Skip,

_[red][/red]

 

[mscallisto]

Thanks Skip ... Go SOX

 

[SkipVought]

But Fisk, Rico, Lynn, Rice, Dewey and Yaz, Rooster, Soup, El Tiante, Butch, The Bird are long gone! Not to mention the Splendid Splinter, who was a bit before my direct recollection.

Skip,

_[red][/red]