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.

28912

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!!:friends:



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