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

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.

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

Sub EF974266()
On Error Resume Next
With ActiveCell
  Range(Cells(1, .Column), Cells(Cells(Rows.Count, .Column + 1).End(xlUp).Row, .Column)).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End With
With ActiveSheet.UsedRange.Columns(ActiveCell.Column)
  .Value = .Value
End With
End Sub

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

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

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] [/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