VBA to autofill columns

**Doctor_H** · 02-26-2013, 10:47 AM

Good morning guys,

I need help with a macro to autofill columns. In the attached sample, column A contains account numbers, column C:E contains other pertinent information that needs to be autofilled along the blank cells following the cell value. As an example, i need the contents of Cells A4, C4, D4, & E4 to copy all the way down to A15, C15, D15, & E15. The same process should repeat through the entire worksheet, which is over 200 pages long. Is this possible with a macro?

Thanks,

Doctor_H

**JBeaucaire** · 02-26-2013, 11:23 AM

This is the technique I use for this:

Option Explicit

Sub FillDownValues()
Dim LR As Long, ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
    ws.Range("A1:E" & LR).SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    With ws.Range("A1:E" & LR)
        .Value = .Value
    End With
    ws.Columns.AutoFit
Next ws

End Sub

**Doctor_H** · 02-26-2013, 02:58 PM

Thanks for the speedy response, Jerry! I tried the code provided and it appears to have worked but I got a run-time error '1004' "No Cells Were Found" after debigging it highlights the line in red below.

Thanks again!

Option Explicit

Sub FillDownValues()
Dim LR As Long, ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
    ws.Range("A1:E" & LR).SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    With ws.Range("A1:E" & LR)
        .Value = .Value
    End With
    ws.Columns.AutoFit
Next ws

End Sub

**JBeaucaire** · 02-26-2013, 04:03 PM

Then your cells are actually empty. That code works on the sample file above. If you have another for me to look at I will do so. Is there a sheet in your workbook that should be skipped?

**Doctor_H** · 02-26-2013, 04:10 PM

No worksheet that should be excluded in the workbook. The only thing I can think of that might be triggering the error are blank rows randomly placed throughout the worksheet. Could this be the issue?