filling a range of a column by macro/function

[Guest]

this has to do with filling a range of empty cells in a column with the value (or string) of the last occupied cell until a cell containing a new value

In Detail, lets just consider one column

typing in data into spreadsheets ends up often with columns where row 1 are filled with a value and e.g. row 8 with a different value (or string). Now rows 2 to 7 must be filled with the same value as row 1. then row 8 to row 23 with the value from row 8 etc ... until a selected end of column character (eg. $ or simply . ) does turn up

I do such manually - fasted using the FILL DOWN (Crtl Dwn) function for this - but in combination with selecting the correct range using keyboard gets tiering for the fingers over time ..

Does sombody has already written a function or macro for such - I'm sure not to be the only user having this to do
Via recording a macro I did not succes because it keeps all the cell references which change each time ..

Thanks for help

Tom Turtle

 

[Andy B]

Tom

Try this:
Select the column you are using.
Then Edit / Go To / Special / Blanks.
Type = and then up arrow
Type Ctrl Enter
Job done

Andy.

this has to do with filling a range of empty cells in a column with the

value (or string) of the last occupied cell until a cell containing a new
value.

In Detail, lets just consider one column:

typing in data into spreadsheets ends up often with columns where row 1

are filled with a value and e.g. row 8 with a different value (or string).
Now rows 2 to 7 must be filled with the same value as row 1. then row 8 to
row 23 with the value from row 8 etc ... until a selected end of column
character (eg. $ or simply . ) does turn up.

I do such manually - fasted using the FILL DOWN (Crtl Dwn) function for

this - but in combination with selecting the correct range using keyboard
gets tiering for the fingers over time ...

Does sombody has already written a function or macro for such - I'm sure

not to be the only user having this to do.

Via recording a macro I did not succes because it keeps all the cell

references which change each time ...

 

[Frank Kabel]

Hi
try the following
- put the following macro in one of your modules:
Sub Fill_column()
Dim rng As Range
Dim cell As Range
Dim fill
Set rng = Selection
If rng.Columns.Count > 1 Then Exit Sub
fill = ""
For Each cell In rng
If cell.Value = "" Then
cell.Value = fill
Else
fill = cell.Value
End If
Next
End Sub


Select the range and invoke this macro. Macro stops at the end of your
selection. You can easily change it to use a 'Stop -character. Try

Sub Fill_column_with_stop()
Dim rng As Range
Dim cell As Range
Dim fill
Set rng = Selection
If rng.Columns.Count > 1 Then Exit Sub
fill = ""
For Each cell In rng
If cell.Value = "" Then
cell.Value = fill
Elseif cell.value="$$$$" then
exit for
else
fill = cell.Value
End If
Next
End Sub

 

[Guest]

Hello Andy B & Frank Kabel

many thanks for your fast help, i tried it and succeeded to put Andy's commands in following 'Zweizeiler'

Sub Fill_cells_down(
' fills down the empty cell in a column until another filled cel
Selection.SpecialCells(xlCellTypeBlanks).Selec
Selection.FormulaR1C1 = "=R[-1]C
End Su

However this needs to select the range beforehand or - in case one selectes the whole columnd - need to be combined with the stop loop at a predefined charachter/string from your proposition, Frank

I also dicovered following webpage:
http://www.contextures.com/xlDataEntry02.htm

Greetings
To

 

[Juan RV]

This way may be easier:

just put the formulas as below, suppose the first text is
in E11, and then are spaces until the next new text (E16).
The formula in F11 is "=E11", and the formula in F12 is
"=IF(E12="",F11,E12)", then copy this forumla (The one in
F12) to the rest of the values, and you will have a
filled colum with the values. Hope it works.

JUAN