Fill in blank cells with the data listed above it

[twmills]

Hello,

I'm looking to fill in columns A and B with the name in the first cell in the data set. For example, if a name is listed in A6, then copy/paste A6 down till it hits a non-blank cell. Which, in this case, that's cell A9. Then I need what's in cell A9 copy/pasted down to then next non-blank. And so, until the table is finished. Then the same thing with column B.

I've attached an example workbook with two tabs (Before and After). Here you'll probably get a better understanding of what I'm looking to accomplish.

Example.xlsm

Much appreciated,
Tim

[p45cal]

Attached has a macro which works on what's selected.
Select a range 2 columns wide and several rows tall then run the macro (click the button).
It doesn't look for the Total rows anywhere so either do this in several blocks, or do the lot in one go and clear the cells on the Total rows.

All it does on the selected range of cells is the equivalent of manually:
Pressing F5 on the keyboard
Clicking the Special… button
Choosing the blanks option
Clicking the OK button
Then (without changing what's selected) pressing the following sequence on the keyboard:
=
up arrow
hold the Ctrl key and press Enter

Then reselecting the original selection, copy, then paste-values in situ.

The code (includes some protections):

Sub blah()
Dim myRng As Range
If Selection.Rows.Count > 1 Then
On Error Resume Next
  Set myRng = Selection.SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    myRng.FormulaR1C1 = "=R[-1]C"
    Selection.Value = Selection.Value
    Else
    MsgBox "no blank cells"
  End If
Else
  MsgBox "select more than one row of cells!"
End If
End Sub

[arnelgp]

it might not look good but will do the job also:

Private Sub FillIn()
    Const INPUT_SHEET As String = "Before"
    Const IGNORE_WORDS As String = "|User|Workflow Activity Category|Workflow Activity|Total|"
    Dim firstrow As Long, lastrow As Long
    Dim i As Integer
    Dim strValue1 As String
    Dim strValue2 As String
    With Sheets(INPUT_SHEET)
        'hard-coded first row
        firstrow = 6
        lastrow = .Cells(.Rows.Count, 4).End(xlUp).Row
        For i = firstrow To lastrow
            'check if the Cell Value is not in IGNORE_WORDS list
            If InStr(1, IGNORE_WORDS, "|" & .Cells(i, 1).Value & "|", vbTextCompare) = 0 And _
                InStr(1, IGNORE_WORDS, "|" & .Cells(i, 2).Value & "|", vbTextCompare) = 0 And _
                InStr(1, IGNORE_WORDS, "|" & .Cells(i, 4).Value & "|", vbTextCompare) = 0 Then
                'it is not then
                If Len(.Cells(i, 1) & "") <> 0 Then
                    strValue1 = .Cells(i, 1)
                Else
                    .Cells(i, 1) = strValue1
                End If
                If Len(.Cells(i, 2) & "") <> 0 Then
                    strValue2 = .Cells(i, 2)
                Else
                    .Cells(i, 2) = strValue2
                End If
            Else
                strValue1 = ""
                strValue2 = ""
            End If
            
        Next
    End With
End Sub

[snb]

This fastest (reading once, writing once) code suffices.

Sub M_snb()
   sn = Sheet1.Cells(5, 4).CurrentRegion.Offset(, -3).Resize(, 4)
   
   For j = 2 To UBound(sn)
      If sn(j, 1) = "" Then sn(j, 1) = sn(j - 1, 1)
      If sn(j, 2) = "" Then sn(j, 2) = sn(j - 1, 2)
   Next
   
   Sheet1.Cells(5, 8).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

[arnelgp]

it is fast, but see the result.
there shouldn't be any Text
when the Row on Column D is "Total" (see "After" sheet).

[twmills]

it might not look good but will do the job also:

Private Sub FillIn()
    Const INPUT_SHEET As String = "Before"
    Const IGNORE_WORDS As String = "|User|Workflow Activity Category|Workflow Activity|Total|"
    Dim firstrow As Long, lastrow As Long
    Dim i As Integer
    Dim strValue1 As String
    Dim strValue2 As String
    With Sheets(INPUT_SHEET)
        'hard-coded first row
        firstrow = 6
        lastrow = .Cells(.Rows.Count, 4).End(xlUp).Row
        For i = firstrow To lastrow
            'check if the Cell Value is not in IGNORE_WORDS list
            If InStr(1, IGNORE_WORDS, "|" & .Cells(i, 1).Value & "|", vbTextCompare) = 0 And _
                InStr(1, IGNORE_WORDS, "|" & .Cells(i, 2).Value & "|", vbTextCompare) = 0 And _
                InStr(1, IGNORE_WORDS, "|" & .Cells(i, 4).Value & "|", vbTextCompare) = 0 Then
                'it is not then
                If Len(.Cells(i, 1) & "") <> 0 Then
                    strValue1 = .Cells(i, 1)
                Else
                    .Cells(i, 1) = strValue1
                End If
                If Len(.Cells(i, 2) & "") <> 0 Then
                    strValue2 = .Cells(i, 2)
                Else
                    .Cells(i, 2) = strValue2
                End If
            Else
                strValue1 = ""
                strValue2 = ""
            End If
            
        Next
    End With
End Sub

Anything I need to do before hand? Getting a debug at With Sheets(INPUT_SHEET)

[twmills]

Actually I think I got what I needed.

Thanks everyone for their help

[twmills]

This actually worked great once I put this in front of it all:

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range("A3:B" & LastRow).Select

That way I didn't have to manually select the two columns.

Thanks so much...this was exactly what I needed!!

Attached has a macro which works on what's selected.
Select a range 2 columns wide and several rows tall then run the macro (click the button).
It doesn't look for the Total rows anywhere so either do this in several blocks, or do the lot in one go and clear the cells on the Total rows.

All it does on the selected range of cells is the equivalent of manually:
Pressing F5 on the keyboard
Clicking the Special… button
Choosing the blanks option
Clicking the OK button
Then (without changing what's selected) pressing the following sequence on the keyboard:
=
up arrow
hold the Ctrl key and press Enter

Then reselecting the original selection, copy, then paste-values in situ.

The code (includes some protections):

Sub blah()
Dim myRng As Range
If Selection.Rows.Count > 1 Then
On Error Resume Next
  Set myRng = Selection.SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    myRng.FormulaR1C1 = "=R[-1]C"
    Selection.Value = Selection.Value
    Else
    MsgBox "no blank cells"
  End If
Else
  MsgBox "select more than one row of cells!"
End If
End Sub

[snb]

it is fast, but see the result.
there shouldn't be any Text
when the Row on Column D is "Total" (see "After" sheet).

You can't be serious; Such simple code can easily be adapted.

Sub M_snb()
  sn = Sheet1.Cells(5, 4).CurrentRegion.Offset(, -3).Resize(, 4)

  For j = 2 To UBound(sn)
    If sn(j,4) <> "Total" Then
       If sn(j, 1) = "" Then sn(j, 1) = sn(j - 1, 1)
       If sn(j, 2) = "" Then sn(j, 2) = sn(j - 1, 2)
    End if
  Next

  Sheet1.Cells(5, 8).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub