VBA: List of blank and non-blank cells
 

Hello,
I have a list that looks as follows:
1012
-blank-
-blank-
1013
-blank-
-blank-
-blank-
and so forth.

What I like to do, is to get a VBA command that fills the blank cells with
the above value until the next value is reached. In this example, first two
blanks with "1012", the other blanks with "1013" until the end of the list is
reached. The end of the list is reached, when there are no more values in the
next column (lets say column B).
Can anybody help?
Thanks in advance!
Mirja

List of blank and non-blank cells
 

I'm assuming -blank- means empty and not "-blank-".
Here's one way:

Sub test()
For Each cell In Range("A1:A7")
If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub

Regards,
Paul

"Mirja" wrote in message
...
Hello,
I have a list that looks as follows:
1012
-blank-
-blank-
1013
-blank-
-blank-
-blank-
and so forth.

What I like to do, is to get a VBA command that fills the blank cells with
the above value until the next value is reached. In this example, first
two
blanks with "1012", the other blanks with "1013" until the end of the list
is
reached. The end of the list is reached, when there are no more values in
the
next column (lets say column B).
Can anybody help?
Thanks in advance!
Mirja

List of blank and non-blank cells
 

John Walkenbach has a easy way to do it at ...
http://j-walk.com/ss/excel/usertips/tip040.htm

------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware






"Mirja"

wrote in message
Hello,
I have a list that looks as follows:
1012
-blank-
-blank-
1013
-blank-
-blank-
-blank-
and so forth.

What I like to do, is to get a VBA command that fills the blank cells with
the above value until the next value is reached. In this example, first two
blanks with "1012", the other blanks with "1013" until the end of the list is
reached. The end of the list is reached, when there are no more values in the
next column (lets say column B).
Can anybody help?
Thanks in advance!
Mirja

VBA: List of blank and non-blank cells
 

You don't need VBA to do this, but if you want VBA here is a Dave Peterson
construct.

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(lastrow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord Dibben MS Excel MVP

On Mon, 30 Oct 2006 09:20:02 -0800, Mirja
wrote:

Hello,
I have a list that looks as follows:
1012
-blank-
-blank-
1013
-blank-
-blank-
-blank-
and so forth.

What I like to do, is to get a VBA command that fills the blank cells with
the above value until the next value is reached. In this example, first two
blanks with "1012", the other blanks with "1013" until the end of the list is
reached. The end of the list is reached, when there are no more values in the
next column (lets say column B).
Can anybody help?
Thanks in advance!
Mirja