Filling In Blank Cells With Code or Macro

[Guest]

Dear fellow Excel 2000 users:

I have a spreadsheet where there are 5 columns and thousands of rows
of data. Every so often, there is missing data in the second and
third column. I wanted to write some code that would, when it came to
a blank cell in column two or three, to simply copy whatever was in
the cell above it.

For instance, here is an example of a few rows before the code is run:
A B C D E
WW WW WW WW WW
XXX XXX XXX XXX XXX
YYY YYY YYY
ZZZ ZZZ ZZZ ZZZ ZZZ

Here is what I am looking for after:

WW WW WW WW WW
XXX XXX XXX XXX XXX
YYY XXX XXX YYY YYY
ZZZ ZZZ ZZZ ZZZ ZZZ

(Basically copy the XXX data down to the blank YYY row)

I searched Google, but could not find anything that did such a task.

If anyone could possible come up with some code, I would GREATLY
appreciate it!

Thanks a million in advance!

Kevin

 

[Frank Kabel]

Hi
pne way:
- select your data range
- hit F5, click 'Special', choose 'blank cells'
- now enter the equation sign '='
- hit the upper arrow key
- confirm your entry with CTRL+ENTER

 

[Gord Dibben]

If you do want a macro......(although not necessary, see Frank's post.)

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 Excel MVP

 

[Guest]

Magnificent.

 

[Ken Wright]

One thing to watch for though, given that you have thousands of rows, if the
total amount of selected areas would exceed 8192 selections then you will get a
message back saying selection too large. If that happens then just try it one
column at a time or break the area down by selecting smaller chunks and try it.