Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
I would like to fill in blank cells in one Column with the value of the
nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
Macro solution:
'A is a column that will always have data in your data set, 'B is the column that has blanks to be filled Sub fill_blanks() For i = 1 to 10000 '<-increase to be larger than your max number of rows if Sheet1.range("A" & i).value="" then exit sub If Sheet1.range("B" & i).value <"" then tempval = Sheet1.range("B" & i).value else Sheet1.range("B" & i).value = tempval endif Next end sub However, and easier solution: insert a new column, and starting in row 2 (B2 in your example) =if(A2<"",A2,,A1) copy it all the way down, then copy/paste special/values over the original column... then delete the (temporary) column that has this formula in it. HTH, Keith "Steve Stad" wrote: I would like to fill in blank cells in one Column with the value of the nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
Give this macro a try...
Sub FillInTheBlanks() Dim Area As Range, LastRow As Long Const ColLetter As String = "A" On Error Resume Next LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row For Each Area In Columns(ColLetter)(1).Resize(LastRow). _ SpecialCells(xlCellTypeBlanks).Areas Area.Value = Area(1).Offset(-1).Value Next End Sub -- Rick (MVP - Excel) "Steve Stad" wrote in message ... I would like to fill in blank cells in one Column with the value of the nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
with and Without a macro examples
http://www.contextures.com/xlDataEntry02.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve Stad" wrote in message ... I would like to fill in blank cells in one Column with the value of the nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
keith - the easy solution does not work b/c when you copy
'=if(A2<"",A2,,A1)' down it copies the blank, i.e., a2 and a3, not the nonblank cell 'a1' above the blank cells. "ker_01" wrote: Macro solution: 'A is a column that will always have data in your data set, 'B is the column that has blanks to be filled Sub fill_blanks() For i = 1 to 10000 '<-increase to be larger than your max number of rows if Sheet1.range("A" & i).value="" then exit sub If Sheet1.range("B" & i).value <"" then tempval = Sheet1.range("B" & i).value else Sheet1.range("B" & i).value = tempval endif Next end sub However, and easier solution: insert a new column, and starting in row 2 (B2 in your example) =if(A2<"",A2,,A1) copy it all the way down, then copy/paste special/values over the original column... then delete the (temporary) column that has this formula in it. HTH, Keith "Steve Stad" wrote: I would like to fill in blank cells in one Column with the value of the nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
Thanks Ron - The video at the web site really helped.
"Ron de Bruin" wrote: with and Without a macro examples http://www.contextures.com/xlDataEntry02.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve Stad" wrote in message ... I would like to fill in blank cells in one Column with the value of the nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill in Blanks
Apologies for the typo, it looks like there is an extra comma in there- and
the value it should copy is the cell above, not the one in A; should be (B1) =A1 (B2) =if(A2<"",A2,B1) then copy the B2 formula down. This also assumes that A2 (and below) is truly blank; if you have a space, formula, or other characters, then this formula would have to be updated accordingly... just giving you some ideas on alternate ways to get a working solution. HTH, Keith "Steve Stad" wrote: keith - the easy solution does not work b/c when you copy '=if(A2<"",A2,,A1)' down it copies the blank, i.e., a2 and a3, not the nonblank cell 'a1' above the blank cells. "ker_01" wrote: Macro solution: 'A is a column that will always have data in your data set, 'B is the column that has blanks to be filled Sub fill_blanks() For i = 1 to 10000 '<-increase to be larger than your max number of rows if Sheet1.range("A" & i).value="" then exit sub If Sheet1.range("B" & i).value <"" then tempval = Sheet1.range("B" & i).value else Sheet1.range("B" & i).value = tempval endif Next end sub However, and easier solution: insert a new column, and starting in row 2 (B2 in your example) =if(A2<"",A2,,A1) copy it all the way down, then copy/paste special/values over the original column... then delete the (temporary) column that has this formula in it. HTH, Keith "Steve Stad" wrote: I would like to fill in blank cells in one Column with the value of the nonblank cell above the blank cells. e.g., automatically fillin A2 and A3 with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro steps? A 1 orange 2 3 4 apple 5 6 7 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fill up blanks | Excel Discussion (Misc queries) | |||
Fill in the blanks! | Excel Programming | |||
Fill in the blanks! | Excel Programming | |||
Fill in the blanks!! | Excel Programming | |||
Fill in the blanks | Excel Programming |