insert a blank in every second line of a row

I have a table with hourly values, but need to use them with half an hour
values. Im therefore trying to make the table twice as long, with blanks in
every second (or the best would be averages in every second) and the "next"
from the original list as the next entry.

Hi Elise,

You could do this with a macro, but for a one-off that's more complicated
than necessary.

Suggested solution:
.. in an empty column on row 1, enter the formula:
=ROW()*2-1
.. copy this down to your last row.
..below you last row, insert the formula:
=A1+1
replacing 'A' with your column letter.
.. copy this formula down for as many rows as you did with the first formula
(hint: the last # should be one more than was calculated with the first
formula
.. copy this column and paste the contents over themselves using Edit|Paste
Special|Values
.. sort your data using the used rows in this column as the sort key.

Cheers

--
macropod
[MVP - Microsoft Word]


"EliseT" <[email protected]> wrote in message
news:[email protected]...
> I have a table with hourly values, but need to use them with half an hour
> values. Im therefore trying to make the table twice as long, with blanks
in
> every second (or the best would be averages in every second) and the
"next"
> from the original list as the next entry.

hey EliseT

This should do the trick, just a once of use short macro

Sub line_space_line()
Range("A1").Select
count = 0
Do Until count = 20
ActiveCell.Offset(1, 0).Select

ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
count = count + 1
Loop


End Sub

Change the first line "Range("A1").Select" to the correct column e.g a for a etc

If you only want it to happen for 20 rows input "Do Until count = 20"

if it is never ending rows just use this code exactly the same but will do the whole column until and empty cell

Sub line_space_line()
Range("A1").Select

Do Until ActiveCell = 0
ActiveCell.Offset(1, 0).Select

ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select

Loop


End Sub


hope it helps


zygan

ive now tried that, but i didnt quite get how i was supposed to sort the
data....

"macropod" wrote:

> Hi Elise,
>
> You could do this with a macro, but for a one-off that's more complicated
> than necessary.
>
> Suggested solution:
> .. in an empty column on row 1, enter the formula:
> =ROW()*2-1
> .. copy this down to your last row.
> ..below you last row, insert the formula:
> =A1+1
> replacing 'A' with your column letter.
> .. copy this formula down for as many rows as you did with the first formula
> (hint: the last # should be one more than was calculated with the first
> formula
> .. copy this column and paste the contents over themselves using Edit|Paste
> Special|Values
> .. sort your data using the used rows in this column as the sort key.
>
> Cheers
>
> --
> macropod
> [MVP - Microsoft Word]
>
>
> "EliseT" <[email protected]> wrote in message
> news:[email protected]...
> > I have a table with hourly values, but need to use them with half an hour
> > values. Im therefore trying to make the table twice as long, with blanks
> in
> > every second (or the best would be averages in every second) and the
> "next"
> > from the original list as the next entry.
>
>
>

thanks a lot! this is the first macro i've ever used in my life...
Elise

"Zygan" wrote:

>
> hey EliseT
>
> This should do the trick, just a once of use short macro
>
> Sub line_space_line()
> Range("A1").Select
> count = 0
> Do Until count = 20
> ActiveCell.Offset(1, 0).Select
>
> ActiveCell.EntireRow.Select
> Selection.Insert Shift:=xlDown
> ActiveCell.Offset(1, 0).Select
> count = count + 1
> Loop
>
>
> End Sub
>
> Change the first line "Range("A1").Select" to the correct column e.g a
> for a etc
>
> If you only want it to happen for 20 rows input "Do Until count = 20"
>
> if it is never ending rows just use this code exactly the same but will
> do the whole column until and empty cell
>
> Sub line_space_line()
> Range("A1").Select
>
> Do Until ActiveCell = 0
> ActiveCell.Offset(1, 0).Select
>
> ActiveCell.EntireRow.Select
> Selection.Insert Shift:=xlDown
> ActiveCell.Offset(1, 0).Select
>
> Loop
>
>
> End Sub
>
>
> hope it helps
>
>
> zygan
>
>
> --
> Zygan
> ------------------------------------------------------------------------
> Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
> View this thread: http://www.excelforum.com/showthread...hreadid=548526
>
>

Hi Elise,

The idea is to select all the rows & columns with your data, plus the added
one, then sort on the added column. But it looks like you don't need this
now ...

Cheers

--
macropod
[MVP - Microsoft Word]


"EliseT" <[email protected]> wrote in message
news:[email protected]...
> ive now tried that, but i didnt quite get how i was supposed to sort the
> data....
>
> "macropod" wrote:
>
> > Hi Elise,
> >
> > You could do this with a macro, but for a one-off that's more
complicated
> > than necessary.
> >
> > Suggested solution:
> > .. in an empty column on row 1, enter the formula:
> > =ROW()*2-1
> > .. copy this down to your last row.
> > ..below you last row, insert the formula:
> > =A1+1
> > replacing 'A' with your column letter.
> > .. copy this formula down for as many rows as you did with the first
formula
> > (hint: the last # should be one more than was calculated with the first
> > formula
> > .. copy this column and paste the contents over themselves using
Edit|Paste
> > Special|Values
> > .. sort your data using the used rows in this column as the sort key.
> >
> > Cheers
> >
> > --
> > macropod
> > [MVP - Microsoft Word]
> >
> >
> > "EliseT" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a table with hourly values, but need to use them with half an
hour
> > > values. Im therefore trying to make the table twice as long, with
blanks
> > in
> > > every second (or the best would be averages in every second) and the
> > "next"
> > > from the original list as the next entry.
> >
> >
> >

youre right; but thanks a lot anyway...


"macropod" wrote:

> Hi Elise,
>
> The idea is to select all the rows & columns with your data, plus the added
> one, then sort on the added column. But it looks like you don't need this
> now ...
>
> Cheers
>
> --
> macropod
> [MVP - Microsoft Word]
>
>
> "EliseT" <[email protected]> wrote in message
> news:[email protected]...
> > ive now tried that, but i didnt quite get how i was supposed to sort the
> > data....
> >
> > "macropod" wrote:
> >
> > > Hi Elise,
> > >
> > > You could do this with a macro, but for a one-off that's more
> complicated
> > > than necessary.
> > >
> > > Suggested solution:
> > > .. in an empty column on row 1, enter the formula:
> > > =ROW()*2-1
> > > .. copy this down to your last row.
> > > ..below you last row, insert the formula:
> > > =A1+1
> > > replacing 'A' with your column letter.
> > > .. copy this formula down for as many rows as you did with the first
> formula
> > > (hint: the last # should be one more than was calculated with the first
> > > formula
> > > .. copy this column and paste the contents over themselves using
> Edit|Paste
> > > Special|Values
> > > .. sort your data using the used rows in this column as the sort key.
> > >
> > > Cheers
> > >
> > > --
> > > macropod
> > > [MVP - Microsoft Word]
> > >
> > >
> > > "EliseT" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a table with hourly values, but need to use them with half an
> hour
> > > > values. Im therefore trying to make the table twice as long, with
> blanks
> > > in
> > > > every second (or the best would be averages in every second) and the
> > > "next"
> > > > from the original list as the next entry.
> > >
> > >
> > >
>
>
>

Thanks macropod.

I am an Excel 2008 user so cannot use macros.

Your solution was a work of genius! Well done!

i think this has been over complicated! to insert a new row every other row just in a spare column 1)number the rows to end of data, 2)copy this range and paste below last number,then 3)sort on that spare column if you wanted to insert 2 rows you would just add the range again at the bottom at step 2 then sort on that, and so on for 4,5 rows whatever