| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Blanks only with ABOVE nonBlanks
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filling Blanks only with ABOVE nonBlanks
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filling Blanks only with ABOVE nonBlanks
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filling Blanks only with ABOVE nonBlanks
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).FormulaR1C 1 = "=R[-1]C" Mike Mike "JMay" wrote: 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filling Blanks only with ABOVE nonBlanks
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" wrote: 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).FormulaR1C 1 = "=R[-1]C" Mike Mike "JMay" wrote: 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" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filling in blanks | Excel Discussion (Misc queries) | |||
| Filling in the blanks methods? | Excel Programming | |||
| Filter for blanks - nonblanks problem | Excel Discussion (Misc queries) | |||
| Access - Filling Blanks | Excel Worksheet Functions | |||
| Filling in blanks. | Excel Worksheet Functions | |||
Hybrid Mode
