Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel vba to fill in blank cells with previous cell value

Posted on 2014-09-23
5
3,986 Views
Last Modified: 2014-09-24
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
0
Comment
Question by:GPSPOW
5 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40340414
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

0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40340418
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
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 40340431
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341161
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
 

Author Closing Comment

by:GPSPOW
ID: 40342016
This worked perfectly.

Took 2 seconds.

Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question