HELP: If cell is blank fill from above - but there is a catch

[jack_daniel]

Hey Guys,

I have the following simple macro to fill the cells - if blank - with the data from the cell above. That works, however there is a catch.

Code:
---------
Sub FillColBlanks_Offset()
'fill blank cells in column with value above

Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, _
LookIn:=xlFormulas).Row
For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub
---------
I want to be able to have the data in the cell selectable from a drop down list. If there is no selection made (cell blank), the cell will populate with the data from the last filled cell above, BUT when you go back and select something from the drop down list, it will automatically repopulate all the pre filled cells with updated data.

eg. A1 selected from drop down list "DATA"

all the cells below automatically fill with "DATA".

however when I go to A17 and manually select the drop down list value of "VALUE" I need all the cells below (A18 - A?) to re-populate with "VALUE" whilst leaving the above cells A2 - A16 as "DATA"

I admit I am NOT a VBA genius, so please be gentle with me.

Thanks in advance.

[HaHoBe]

Hi, jack_daniel,

you would need to wrap your procedure with code-tags as requested by Forum Rule #3 instead of displaying in in red colour.

For taking care of thr empty cells to be filled you might consider to use

Please Login or Register  to view this content.

The catch as you call it shall take care of any cells empty or already filled? Should that be started by an event like changing/entering data?

Ciao,
Holger

[kalak]

to fill blank cells from above from either selected or code-specified cell, try

Please Login or Register  to view this content.

[kalak]

whoops, mustn't allow bugs, try

Please Login or Register  to view this content.

[jack_daniel]

Kalak,

Thanks - but that didn't seem to do anything

[jack_daniel]

Forgot to mention I need to start at row 8

[HaHoBe]

Hi, jack_daniel,

please have a look at my signature in the first post on how to apply code tags.

Forgot to mention I need to start at row 8

From the opening post:

whilst leaving the above cells A2 - A16 as "DATA"

Like mentioned: I see two different codes necessary here.

Ciao,
Holger

[kalak]

Earlier code started from active cell, thus required you to make a sheet selection first.
You can manually select A8, if it's column A you're using, or to define the start cell with the code, replace the second line by

Please Login or Register  to view this content.

[jack_daniel]

Kalak,

Sorry mate, still does nothing.

Tried starting from the active cell, C8, selecting all the cells I want replaced, selecting the column, seems to do nothing - did I miss something?

Thanks

[HaHoBe]

Hi, jack_daniel,

Your opening post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[kalak]

Holger,
You're a tough taskmaster. Keep it up!


Jack,
I need to be elsewhere. Good luck!