how to insert ALTERNATING ROWS via VBA

[teletubby5e]

I need a little help with what seemed to me a simple task.

I am just trying to insert a row on every alternating line in a spreadsheet, and for it to stop when it gets to the bottom. something along these lines.

1. start in the cell where the cursor is.
2. insert a line
3. if it as at the last row, stop
4. if not, move down 2 lines
5. go back to 1

i tried modifying a great script in this forum which shaded alternating lines using a MOD command, but no luck yet.

i imagine it has something to do with the (offset 1,0) command.

can someone please point me in the right direction?

thanks, jeff

 

[SkipVought]

Hi, Jeff,

I usually avoid INSERT.

I'd create a column and number the rows of data odd or even as the case may be (2, 4, 6 or 1, 3, 5...).

Then I'd continue with in the same column and and with the LAST GENERATED NUMBER, incriment in reverse, even or odd.

Then I'd SORT on that column.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[SkipVought]

And if you insist on INSERT, have you tried using your macro recorder? That's often one of the BEST places to begin.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[teletubby5e]

THANKS. BUT I AM NOT TRYING TO PERFORM ANY SORTING THOUGH. AND THE INSERT IS WHAT I AM TRYING TO DO, INSERT A BLANK LINE.

HERE IS THE TROUBLE THAT I RAN INTO WHEN I DID THE MACRO RECORD THIS MORNING. IT WANTS TO START AT THE CELL NUMBERS I WAS DOING THE RECORDING IN, NOT THE ACTIVE CELL.

Selection.EntireRow.Insert
Range("A3").Select
Selection.EntireRow.Insert
Range("A5").Select
Selection.EntireRow.Insert
Range("A7").Select
Selection.EntireRow.Insert
Range("A9").Select
Selection.EntireRow.Insert

I WAS GOING TO PUT IT IN A FOR NEXT LOOP, BUT I WANT TO FIND A WAY TO DO THIS UNTIL IT HITS THE LAST ROW. I AM STILL PLAYING WITH IT THOUGH, THAT IS WHY THE FORUM IS GOOD, I USUALLY HIT THAT "DOH!" MOMENT OF REALIZATION ABOUT 5 OR 6 POSTS INTO THE PROCESS ....

THANKS, AGAIN, JEFF

 

[PHV]

Have a look in the FAQ area to discover at least 2 different ways of getting the last used row number.
Tip: insert your rows backward from end to start.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

The SORTING would be to put an empty row between each existing row.

First find the LAST ROW --

lLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Then you do a For Next, Step -1 and INSERT on each row.



Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[teletubby5e]

AH GOT IT. THANKS.