Fill Down, Column E

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 19 Mar 2008 19:57 GMT

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---

Signature

RyGuy

Reply to this Message

Ken Hudson - 19 Mar 2008 21:14 GMT

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

Signature

Ken Hudson

> 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
[quoted text clipped - 16 lines]
> Regards,
> Ryan---

Reply to this Message

ryguy7272 - 19 Mar 2008 21:21 GMT

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---

Signature

RyGuy

> Hi Ryan,
>
[quoted text clipped - 43 lines]
> > Regards,
> > Ryan---

Reply to this Message

Ken Hudson - 19 Mar 2008 22:30 GMT

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

Signature

Ken Hudson

> That's exactly it! I kept thinking I needed something like this:
> Selection.FormulaR1C1 = "=R[-1]C"
[quoted text clipped - 51 lines]
> > > Regards,
> > > Ryan---

Reply to this Message