-1

Sorry for the noobish question, but I'm new and I don't have much of a programming background. I tried this on my own for a few hours, but I just don't know enough. I checked this and this, but I couldn't figure out how to modify it enough.

Example data:

enter image description here

The first part is how I get the file, the 2nd part is how I want it to look.

The first 3 columns have values somewhere in the column. I need to find those values, copy them, and paste them down to the next value, then repeat all the way to the bottom of the range. Sometimes there are many values per column, sometimes only 1. The last row of the data could be determined by column 4. Basically I just need to fill in all the blank cells. Note: Row 2 does not always contain the first value.

Here's what I have so far (update):

Sub FindFillIn()

    Dim columnValues  As Range, i As Long
    Dim cellstart As Integer
    Dim cellend As Integer
    cellstart = 2
    cellend = ActiveSheet.Range("E" & ActiveSheet.Rows.Count).End(xlUp).Row

    i = 1

    For i = cellstart To cellend
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = Cells(i - 1, 1).Value
        End If
    Next i

End Sub

Update:

It appears to run correctly on the first column, but it only does one column. How do I get it to run on columns 1, 2, and 3?

Community
  • 1
  • 1
ZubaZ
  • 69
  • 2
  • 11
  • 1
    http://stackoverflow.com/questions/8499167/fill-non-contiguous-blank-cells-with-the-value-from-the-cell-above-the-first-bla – Siddharth Rout Feb 03 '14 at 17:20
  • 1
    http://stackoverflow.com/questions/3762340/fill-non-contiguous-blank-cells-with-the-value-from-the-cell-above-the-first-bla – Siddharth Rout Feb 03 '14 at 17:21
  • Thanks for the resources. I read through all of these and I am closer to getting it to work right. I think I just have to figure out how to define the correct selection. – ZubaZ Feb 03 '14 at 20:28

2 Answers2

0
Sub FillInBlanks()

    Dim rng As Range

    On Error Resume Next
    With ActiveSheet
        Set rng = .Range(.Range("A2"), _
         .Cells(Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeBlanks)
    End With
    On Error GoTo 0

    If Not rng Is Nothing Then
        With rng
            .FormulaR1C1 = "=R[-1]C"
            .Value = .Value
        End With
    End If


End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • When I run this code it just puts "header 2" in every empty cell. I tried changing the code, but I can't figure out what it's repeating B1 each time. – ZubaZ Feb 03 '14 at 20:26
  • Is the top row of data complete, as in your example? I was assuming your actual data begin on row2. – Tim Williams Feb 03 '14 at 20:33
  • oops, that was my bad. The top row (or row 2) does not always contain the first piece of data. There usually are a few blank rows in columns 1-3 that come through at the top first. – ZubaZ Feb 03 '14 at 20:36
0

This is easy without using VB. Select the data of column 1 until the last cell where you want to fill with the data. Press CTRL+G and click on Special. Select Blank and press OK. Now, on the formula bar, type "=" and select (pressing Ctrl+Click) the first Cell where the data start and then Control + Enter.

You can record this process to a macro and then view the code.

Angel Diaz
  • 36
  • 5
  • This info is very useful. Thanks. I'm actually going to use this for something else I'm working on, although I really still need to get VB working for this question. – ZubaZ Feb 03 '14 at 20:30