insert a blank in every second line of a row

E

EliseT

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.
 
M

macropod

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
 
Z

Zygan

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
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 wil
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


zyga
 
E

EliseT

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

macropod said:
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 said:
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.
Click to expand...
Click to expand...
 
M

macropod

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 said:
ive now tried that, but i didnt quite get how i was supposed to sort the
data....

macropod said:
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 said:
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
Click to expand...
blanks
in
every second (or the best would be averages in every second) and the "next"
from the original list as the next entry.
Click to expand...
Click to expand...
Click to expand...
 
E

EliseT

youre right; but thanks a lot anyway...


macropod said:
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 said:
ive now tried that, but i didnt quite get how i was supposed to sort the
data....

macropod said:
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]


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.
Click to expand...
Click to expand...
Click to expand...
 
Top