Auto fill blank columns in a row

**excelnoob927** · 04-08-2015, 02:17 PM

Hey all -

I have a list of people in a bunch of rows, with their superior in the column to the right. Some are further down the line in the org, so they have more managers and fill up more columns. Others have only 1 manager and only fill up the first column to the right of the name.

Is there any way to write an equation / code that autofills each row? I would need columns R-V (everyone has Q filled with the CEO) to be filled with the previous cell in the row if R, S, T, U, or V is blank. If they are all blank, I would want them to fill with the same name as column Q.

I've attached an example of what I have vs what i Need, for your reference.

Thnaks!
-EN

**sktneer** · 04-08-2015, 02:59 PM

Try this......

Option Explicit

Sub FillBlankCells()
Dim i As Long, j As Long, lr As Long, lc As Long
lr = Cells(Rows.Count, "P").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 2 To lr
        For j = 18 To lc - 1
                If Cells(i, j) = "" Then
                        Cells(i, j) = Cells(i, j - 1)
                End If
        Next j
Next i
Application.ScreenUpdating = True
End Sub

Please find the attached workbook and click on the Blue Button on the Test Sheet to run the macro. The code is present on Module1.