Hi!

I hope some of you will help me with this problem. I guess it is quite
simple, but I keep on getting it wrong.

My task is to fill data in the blank cells of a column with the data
already in the column. The data of the first row should be filled to
the following rows until a new default data appears. Then this data
should be filled to the next rows of this column, etc.
Example: Cell A1 = 34. Cells A2-A5 are blank. Cell A6 = 65. Cells
A7-A30 are blank, and so on. I want cells A2-A5 to be filled with "34",
cells A7-A30 to be filled with "65" and so on until a specified end.

Can anyone help me?

Thanks!!

Magnus Røed

Sub fill_blanks()
For r = 2 To 50 'Change 50 to suit your last row
If Cells(r, 1) = "" Then Cells(r, 1) = Cells(r - 1, 1)
Next r
End Sub


--
Ian
--
"Mags" wrote in message
oups.com...
Hi!

I hope some of you will help me with this problem. I guess it is quite
simple, but I keep on getting it wrong.

My task is to fill data in the blank cells of a column with the data
already in the column. The data of the first row should be filled to
the following rows until a new default data appears. Then this data
should be filled to the next rows of this column, etc.
Example: Cell A1 = 34. Cells A2-A5 are blank. Cell A6 = 65. Cells
A7-A30 are blank, and so on. I want cells A2-A5 to be filled with "34",
cells A7-A30 to be filled with "65" and so on until a specified end.

Can anyone help me?

Thanks!!

Magnus Røed

and just to add to ian's great solution, you can use the lastrow variable
instead of a specific cell

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

--


Gary


"Mags" wrote in message
oups.com...
Hi!

I hope some of you will help me with this problem. I guess it is quite
simple, but I keep on getting it wrong.

My task is to fill data in the blank cells of a column with the data
already in the column. The data of the first row should be filled to
the following rows until a new default data appears. Then this data
should be filled to the next rows of this column, etc.
Example: Cell A1 = 34. Cells A2-A5 are blank. Cell A6 = 65. Cells
A7-A30 are blank, and so on. I want cells A2-A5 to be filled with "34",
cells A7-A30 to be filled with "65" and so on until a specified end.

Can anyone help me?

Thanks!!

Magnus Røed

Here is a non-looping approach if your column doesn't contain formulas:

Sub FillBlanks()
Dim rng As Range
Dim rng1 As Range

With ActiveSheet
Set rng = Intersect(.Columns(1), .UsedRange).Cells
End With
Set rng1 = rng.SpecialCells(xlBlanks)
rng1.Formula = "=" & rng1(0, 1).Address(0, 0)
rng.Formula = rng.Value
End Sub

--
Regards,
Tom Ogilvy


"Mags" wrote in message
oups.com...
Hi!

I hope some of you will help me with this problem. I guess it is quite
simple, but I keep on getting it wrong.

My task is to fill data in the blank cells of a column with the data
already in the column. The data of the first row should be filled to
the following rows until a new default data appears. Then this data
should be filled to the next rows of this column, etc.
Example: Cell A1 = 34. Cells A2-A5 are blank. Cell A6 = 65. Cells
A7-A30 are blank, and so on. I want cells A2-A5 to be filled with "34",
cells A7-A30 to be filled with "65" and so on until a specified end.

Can anyone help me?

Thanks!!

Magnus Røed