Auto fill values with a blank row formatting between entries
 

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

Auto fill values with a blank row formatting between entries
 

If you are looking to autofill the blank rows; try the below

1. Select the data range including blank cells..
2. Press F5. From Goto windowSpecial from options select 'Blanks'
3. This will select all blanks. Please note that the activate cell is the
first blank row. Now press = and press UpArrow to the cell just above.
4. Keep the selection and the reference. press Ctrl+ Enter


If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

Auto fill values with a blank row formatting between entries
 

I just want to autofill the row with the same data set 1-5 with blank
dilineators beween the numbers instead of having adjacent rows:

Befo After:
1 1
2
3 2
4
5 3

4

5


"Jacob Skaria" wrote:

If you are looking to autofill the blank rows; try the below

1. Select the data range including blank cells..
2. Press F5. From Goto windowSpecial from options select 'Blanks'
3. This will select all blanks. Please note that the activate cell is the
first blank row. Now press = and press UpArrow to the cell just above.
4. Keep the selection and the reference. press Ctrl+ Enter


If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

Auto fill values with a blank row formatting between entries
 

With data as below; try the below macro which will insert row in between each
entry..If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook. Run macro from Tools|Macro|Run <selected macro()

Col A
1
2
3
4
5

Sub Macro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
Rows(lngRow).Insert
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

I just want to autofill the row with the same data set 1-5 with blank
dilineators beween the numbers instead of having adjacent rows:

Befo After:
1 1
2
3 2
4
5 3

4

5


"Jacob Skaria" wrote:

If you are looking to autofill the blank rows; try the below

1. Select the data range including blank cells..
2. Press F5. From Goto windowSpecial from options select 'Blanks'
3. This will select all blanks. Please note that the activate cell is the
first blank row. Now press = and press UpArrow to the cell just above.
4. Keep the selection and the reference. press Ctrl+ Enter


If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

Auto fill values with a blank row formatting between entries
 

I don't have much experience with macros. Trying to follow your directions,
I was having trouble saving the macro. Isn't there a simple way to do this
in Excel? The procedure I described used to work very simply.

"Jacob Skaria" wrote:

With data as below; try the below macro which will insert row in between each
entry..If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook. Run macro from Tools|Macro|Run <selected macro()

Col A
1
2
3
4
5

Sub Macro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
Rows(lngRow).Insert
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

I just want to autofill the row with the same data set 1-5 with blank
dilineators beween the numbers instead of having adjacent rows:

Befo After:
1 1
2
3 2
4
5 3

4

5


"Jacob Skaria" wrote:

If you are looking to autofill the blank rows; try the below

1. Select the data range including blank cells..
2. Press F5. From Goto windowSpecial from options select 'Blanks'
3. This will select all blanks. Please note that the activate cell is the
first blank row. Now press = and press UpArrow to the cell just above.
4. Keep the selection and the reference. press Ctrl+ Enter


If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

Auto fill values with a blank row formatting between entries
 

I don't recall any simple formula (like =A1) that would work that way.

But there are formulas that you can use:

If your data is in A1:A#### (starting in row 1), then put this in B1:
=INDEX(A:A,(ROW()+1)/2)

And use your technique.



Excel Autofill wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

--

Dave Peterson

Auto fill values with a blank row formatting between entries
 

With your formula, I get the following:

Original: =INDEX(A:A,(ROW()+1)/2) I want:
1 1 1
2 0
3 3 2
4 0
5 5 3

4

5

I think this must be an options issue, not a formula needed.


"Dave Peterson" wrote:

I don't recall any simple formula (like =A1) that would work that way.

But there are formulas that you can use:

If your data is in A1:A#### (starting in row 1), then put this in B1:
=INDEX(A:A,(ROW()+1)/2)

And use your technique.



Excel Autofill wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

--

Dave Peterson

Auto fill values with a blank row formatting between entries
 

Hi

With your data starting fromw row1 of Col A. use the below formula in B1 and
copy down as required. Try and feedback

=IF(MOD(ROW(),2),INDEX(A:A,(ROW()-1)/2+1),"")


--
If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

I don't have much experience with macros. Trying to follow your directions,
I was having trouble saving the macro. Isn't there a simple way to do this
in Excel? The procedure I described used to work very simply.

"Jacob Skaria" wrote:

With data as below; try the below macro which will insert row in between each
entry..If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook. Run macro from Tools|Macro|Run <selected macro()

Col A
1
2
3
4
5

Sub Macro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
Rows(lngRow).Insert
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

I just want to autofill the row with the same data set 1-5 with blank
dilineators beween the numbers instead of having adjacent rows:

Befo After:
1 1
2
3 2
4
5 3

4

5


"Jacob Skaria" wrote:

If you are looking to autofill the blank rows; try the below

1. Select the data range including blank cells..
2. Press F5. From Goto windowSpecial from options select 'Blanks'
3. This will select all blanks. Please note that the activate cell is the
first blank row. Now press = and press UpArrow to the cell just above.
4. Keep the selection and the reference. press Ctrl+ Enter


If this post helps click Yes
---------------
Jacob Skaria


"Excel Autofill" wrote:

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.