Filling Blanks only with ABOVE nonBlanks

J

JMay

I have Column A8:A150
A8 = SI-11
A9 Blank
A10 Blank
A11 Blank
A12 = SI-12
A13 Blank
A14 Blank
A15 = SI-13
.......

To acheive what I want - Which is:
A8 = SI-11
A9 = SI-11
A10 =SI-11
A11 =SI-11
A12 = SI-12
A13 =SI-12
A14 =SI-12
A15 = SI-13
.......

When within Excel (No VBA)
I highlight A9:A14, Edit, GoTo, SpecialCells.. Blanks, OK
then with 1st cell highlite (by excel as A9) I arrow-up to A8
and Press Control+Enter -- It works FIne

When I record a Macro to do same
I get A9:A14 = SI-11 << the macro is overwriting my NON-Balank Cells
rather than reproducing from where they start downward to the next Non Blank;
I'm not hoding my mouth right, or What?

My current code:

Set mynewRng = Range("A9:A" & MylrA)
With mynewRng
.SpecialCells(xlCellTypeBlanks).Select
.FormulaR1C1 = "=R[-1]C"
End With


Thanks in advance for any assistance....

Jim
 
M

Mike H

Hi,

Try this VB solution

Sub Fill_EM_UP()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A9:A" & lastrow)
For Each C In MyRange
If C.Value = "" Then
C.Value = C.Offset(-1).Value
End If
Next
End Sub

Mike
 
J

JMay

Thanks Mike, works great..
I guess what I was trying just doesn't work.
Is there a specific reason it doesn't?
Thanks again..
Jim

Mike H said:
Hi,

Try this VB solution

Sub Fill_EM_UP()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A9:A" & lastrow)
For Each C In MyRange
If C.Value = "" Then
C.Value = C.Offset(-1).Value
End If
Next
End Sub

Mike

JMay said:
I have Column A8:A150
A8 = SI-11
A9 Blank
A10 Blank
A11 Blank
A12 = SI-12
A13 Blank
A14 Blank
A15 = SI-13
......

To acheive what I want - Which is:
A8 = SI-11
A9 = SI-11
A10 =SI-11
A11 =SI-11
A12 = SI-12
A13 =SI-12
A14 =SI-12
A15 = SI-13
......

When within Excel (No VBA)
I highlight A9:A14, Edit, GoTo, SpecialCells.. Blanks, OK
then with 1st cell highlite (by excel as A9) I arrow-up to A8
and Press Control+Enter -- It works FIne

When I record a Macro to do same
I get A9:A14 = SI-11 << the macro is overwriting my NON-Balank Cells
rather than reproducing from where they start downward to the next Non Blank;
I'm not hoding my mouth right, or What?

My current code:

Set mynewRng = Range("A9:A" & MylrA)
With mynewRng
.SpecialCells(xlCellTypeBlanks).Select
.FormulaR1C1 = "=R[-1]C"
End With


Thanks in advance for any assistance....

Jim
Click to expand...
Click to expand...
 
M

Mike H

Hi,

Glad I could help. The reason your method failed is that you selected the
blanks in the range OK but then did nothing with that you applied the formula
not to the selecetd blanks but to the range. To make your method work do this

MylrA = Cells(Rows.Count, "A").End(xlUp).Row
Set mynewRng = Range("A9:A" & MylrA)
mynewRng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

In my modified code i apply the formula to the selection and not the range

In practice i wouldn't select at all I'd do this

MylrA = Cells(Rows.Count, "A").End(xlUp).Row
Set mynewRng = Range("A9:A" & MylrA)
mynewRng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Mike

Mike

JMay said:
Thanks Mike, works great..
I guess what I was trying just doesn't work.
Is there a specific reason it doesn't?
Thanks again..
Jim

Mike H said:
Hi,

Try this VB solution

Sub Fill_EM_UP()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A9:A" & lastrow)
For Each C In MyRange
If C.Value = "" Then
C.Value = C.Offset(-1).Value
End If
Next
End Sub

Mike

JMay said:
I have Column A8:A150
A8 = SI-11
A9 Blank
A10 Blank
A11 Blank
A12 = SI-12
A13 Blank
A14 Blank
A15 = SI-13
......

To acheive what I want - Which is:
A8 = SI-11
A9 = SI-11
A10 =SI-11
A11 =SI-11
A12 = SI-12
A13 =SI-12
A14 =SI-12
A15 = SI-13
......

When within Excel (No VBA)
I highlight A9:A14, Edit, GoTo, SpecialCells.. Blanks, OK
then with 1st cell highlite (by excel as A9) I arrow-up to A8
and Press Control+Enter -- It works FIne

When I record a Macro to do same
I get A9:A14 = SI-11 << the macro is overwriting my NON-Balank Cells
rather than reproducing from where they start downward to the next Non Blank;
I'm not hoding my mouth right, or What?

My current code:

Set mynewRng = Range("A9:A" & MylrA)
With mynewRng
.SpecialCells(xlCellTypeBlanks).Select
.FormulaR1C1 = "=R[-1]C"
End With


Thanks in advance for any assistance....

Jim
Click to expand...
Click to expand...
Click to expand...
 
J

JMay

Mike - This was MOST helpful - I better understand the grass roots answers,
thanks to your offering such a fine explanation. I'm getting there !!!
Jim May

Mike H said:
Hi,

Glad I could help. The reason your method failed is that you selected the
blanks in the range OK but then did nothing with that you applied the formula
not to the selecetd blanks but to the range. To make your method work do this

MylrA = Cells(Rows.Count, "A").End(xlUp).Row
Set mynewRng = Range("A9:A" & MylrA)
mynewRng.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

In my modified code i apply the formula to the selection and not the range

In practice i wouldn't select at all I'd do this

MylrA = Cells(Rows.Count, "A").End(xlUp).Row
Set mynewRng = Range("A9:A" & MylrA)
mynewRng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Mike

Mike

JMay said:
Thanks Mike, works great..
I guess what I was trying just doesn't work.
Is there a specific reason it doesn't?
Thanks again..
Jim

Mike H said:
Hi,

Try this VB solution

Sub Fill_EM_UP()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A9:A" & lastrow)
For Each C In MyRange
If C.Value = "" Then
C.Value = C.Offset(-1).Value
End If
Next
End Sub

Mike

:

I have Column A8:A150
A8 = SI-11
A9 Blank
A10 Blank
A11 Blank
A12 = SI-12
A13 Blank
A14 Blank
A15 = SI-13
......

To acheive what I want - Which is:
A8 = SI-11
A9 = SI-11
A10 =SI-11
A11 =SI-11
A12 = SI-12
A13 =SI-12
A14 =SI-12
A15 = SI-13
......

When within Excel (No VBA)
I highlight A9:A14, Edit, GoTo, SpecialCells.. Blanks, OK
then with 1st cell highlite (by excel as A9) I arrow-up to A8
and Press Control+Enter -- It works FIne

When I record a Macro to do same
I get A9:A14 = SI-11 << the macro is overwriting my NON-Balank Cells
rather than reproducing from where they start downward to the next Non Blank;
I'm not hoding my mouth right, or What?

My current code:

Set mynewRng = Range("A9:A" & MylrA)
With mynewRng
.SpecialCells(xlCellTypeBlanks).Select
.FormulaR1C1 = "=R[-1]C"
End With


Thanks in advance for any assistance....

Jim
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove specific cells in a column. 2
Merge Append all sheet 1
how to use an formula to transpose a list(left) to be like the right? 2
Move data from first 7 rows from one column A into multiple Columnsthen the next 7 rows 2
creating multiple worksheets from one 0
Which zones are touch given this range 2
Filling in the blanks methods? 3
help with datedif function please 5

Top