asked on

Excel vba to fill in blank cells with previous cell value

I have a large transaction file, 250,000 lines, and therre are approximately 39,000 where the date cells are blank. I want to be able to loop through the file and replace a blank cell with the value in the cell above.

Here is a small sample of the data:

Transaction Date Acct Code TxnCode
4/13/2010      00006350       84443
4/13/2010      00006350      36415
4/13/2010      00006350      43239
       00006350        43239
       00006358        85025
       00006358        36415
4/14/2010      00005143       19120
4/14/2010      00005270       81025

What would be the vba code to loop through these lines and replace the transaction date values?

Thanks

Glen

Martin Liss

Assuming the date is in column 1

Sub FillIn()

Dim strlast As String
Dim lngRow As Long

strlast = Range("A2")

For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
    If Cells(lngRow, 1).Text = "" Then
        Cells(lngRow, 1).Value = strlast
    Else
        strlast = Cells(lngRow, 1).Text
    End If
Next

End Sub

Open in new window

Haris Dulic

Here is the code you can use :

Sub check_n_fill
Dim range As range, cell As range, value As String
Set range = range("A2:A9")
For Each cell In range
If Trim(cell.value) <> "" Then
value = cell.value
Else
cell.value = value
End If
Next cell
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

You don't have to use VBA unless you particularly want to. Downside of using VBA is that you lose your Undo history.

Apply an AutoFilter to the data and set the filter on column A such that it shows only blanks. In the first blank cell type a formula linking it to the previous row, eg if it is row 4 type =A3

Select this cell and the remainder of the blank cells in column A as a block and press Ctrl + D to fill down. This will only populate the visible cells leaving the hidden/filtered cells with theie existing values.

Remove the filter and copy/paste values if so required.

Thanks
Rob H

GPSPOW

ASKER

This worked perfectly.

Took 2 seconds.

Thanks