Fill Down, Column E

[ryguy7272]

I am trying to fill a range in Column E, where there is data in E5 & E10, and
I wanted to be able to auto fill down, but only the blank cells. I wanted
the last row in Column E to be filled down to the last row in Column A that
is used. My macro doesn’t seem to do anything.

Sub AutoFill()

Dim myR As Range
Set myR = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Resize(1, 5)

On Error Resume Next
myR.AutoFill Destination:=Range _
(myR, myR.Offset(0, 5).End(xlDown).Offset(0, -1))

End Sub

Can someone please help out?

Regards,
Ryan---

 

[Ken Hudson]

Hi Ryan,

Does work for you?

Option Explicit
Sub AutoFill()
Dim CountRows As Double
Dim Iloop As Double

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

CountRows = Cells(Rows.Count, "A").End(xlUp).Row

For Iloop = 2 To CountRows
If IsEmpty(Cells(Iloop, "E")) Then
Cells(Iloop, "E") = Cells(Iloop - 1, "E")
End If
Next Iloop

'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[ryguy7272]

That's exactly it! I kept thinking I needed something like this:
Selection.FormulaR1C1 = "=R[-1]C"

Well, thanks for getting that straightened out for me!!!



Regards,
Ryan---

 

[Ken Hudson]

Is the additional line of code what you wanted to know?

Option Explicit

Sub CopyCell1()
Dim X As Long
Dim Sh As Variant
For Each Sh In Array("M", "P", "T", "A", "Mi", "Sm", "H")
For X = 7 To 500
Sheets(Sh).Cells(X, "X") = (Sheets(1).Range("A1") /
Sheets(1).Range(C20)) * 52
Sheets(Sh).Cells(X, "Y") = Sheets(1).Range("C1") +
Sheets(1).Range("D1")
Next
Next
End Sub
--
Ken Hudson

That's exactly it! I kept thinking I needed something like this:
Selection.FormulaR1C1 = "=R[-1]C"

Well, thanks for getting that straightened out for me!!!



Regards,
Ryan---


--
RyGuy

Hi Ryan,

Does work for you?

Option Explicit
Sub AutoFill()
Dim CountRows As Double
Dim Iloop As Double

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

CountRows = Cells(Rows.Count, "A").End(xlUp).Row

For Iloop = 2 To CountRows
If IsEmpty(Cells(Iloop, "E")) Then
Cells(Iloop, "E") = Cells(Iloop - 1, "E")
End If
Next Iloop

'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub