Filling In Blank Cells With Code or Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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.
 
Back
Top