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
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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

1
Haris DulicCommented:
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

0
Wayne Taylor (webtubbs)Commented:
Hi Glen,

Try this routine. Adjust the Range specified to suit your needs.

Sub FillBlanks()

    Dim rng As Range
    Set rng = Range("A2:A33") 'the range containing the blank cells that you wish to fill
    
    With rng.SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    
End Sub

Open in new window


Regards,

Wayne
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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
0
GPSPOWAuthor Commented:
This worked perfectly.

Took 2 seconds.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.