Solved: Fill Blank Cells with Value to the Left

[willhh3]

Hello,
I need to figure out how to fill blank cells in column b with the data that is in the cell to the immediate left. I believe this would be something like "if b2 is is blank, then copy a2 to b2." The code would need to check each row, to the last row with data (which varies week to week). This will be inserted into a larger macro that already has a public function that returns last row number for other parts of the larger macro. So I'd like to be able to use that to help with getting to the last row, or does that make sense? Here is the code for the public function just in case it does:

[vba]Public Function LastRow() As Long
'
' Function to return last row number with content in active sheet
Dim lr As Long
lr = ActiveSheet.UsedRange.Rows.Count
LastRow = lr
End Function
[/vba]
Sample attached.

Thanks in advance for your help.

Whh3

[mikerickson]

With ThisWorkbook.Sheets("Sheet1").Range("A:A")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        With .Resize(.Rows.Count + 1, .Columns.Count).Offset(0, 1)
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"
            .Value = .Value
        End With
    End With
End With

[willhh3]

Thank you for the quick reply mikerickson! The code workes perfectly and it even handled a prameter that I forgot to mention (i.e. Col A has Joe in it and Col B has someone elses name in it - which is okay). That's why I couldn't do a straight copy/delete.

What would we need to add to this to cover a situation where the names are already completely filed in in Col B. This happens sometimes where the code will just need to look and confirm there are no blanks to be filled in. Does that make sense? Thanks again!

[mikerickson]

I think that that code does account for that. Could you attach an example file where that is a problem.

[willhh3]

Maybe I'm doing something wrong. Here is what I did:

- pasted the code into the vba which you can see in the attached screen shot. It's in the "This Workbook" module for the moment.
- ran the maco which filled in the names.
- ran the macro a second time with the names alread filled in and it throws the Run-time error '1004': Application-defined or object defined error.
- created a new spreadsheet and added the same macro. First run went fine, second run went fine and then the 3rd time it started throwing the same error. Each run after that it now throws the error. If I add some blanks back in. It will run the first time then throw the error.

Including the original spreadsheet (may have to make a few blank cells) and the screenshot.

Thanks for your time!

[willhh3]

Here is the sample file.

[Paul_Hossler]

I'd do something like this

[VBA]
Sub test()
Dim rBlanks As Range

Set rBlanks = Nothing
With ThisWorkbook.Sheets("Sheet1")
On Error Resume Next
Set rBlanks = Intersect(.Range("B:B"), .UsedRange).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rBlanks Is Nothing Then
rBlanks.FormulaR1C1 = "=RC[-1]"
Intersect(.Range("B:B"), .UsedRange).Copy
.Range("B1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
End With
End Sub
[/VBA]

Paul

[mikerickson]

This should work

[VBA]Sub test()
With ThisWorkbook.Sheets("Sheet1").Range("A:A")
With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
With .Offset(0, 1)
.Value = .Value
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]&"""""
On Error GoTo 0
.Value = .Value
End With
End With
End With
End Sub[/VBA]

[willhh3]

Thanks to you both. Both solutions work perfectly. I'm marking this as solved!