Macro Filldown
 

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize(, 1)
Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


Above is a simple macro filling down a formula from cell F5. I'm running
into a problem where it exceeds my list by 4 lines. How can I prevent this.
My goal is to have rows 1-4 blank and the fill down end at the last piece of
information.

Thanks in advance.

Change your resize command from

..Resize(, 1) to
..Resize(usedrange.rows.count-4, 1)


"Hirsch" wrote:

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize(, 1)
Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


Above is a simple macro filling down a formula from cell F5. I'm running
into a problem where it exceeds my list by 4 lines. How can I prevent this.
My goal is to have rows 1-4 blank and the fill down end at the last piece of
information.

Thanks in advance.

I'm not sure what the error is in the formula. I've applied the resize used
range componnent but now get an error. Listed below is what now have. Did I
miss anything?

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize
UsedRange.Rows.Count - 4, 1)

Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


"Hirsch" wrote:

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize(, 1)
Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


Above is a simple macro filling down a formula from cell F5. I'm running
into a problem where it exceeds my list by 4 lines. How can I prevent this.
My goal is to have rows 1-4 blank and the fill down end at the last piece of
information.

Thanks in advance.

try

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5). _
Resize( activesheet.UsedRange.Rows.Count - 4, 1)

Sorry about not catching that on the first go-round

"Hirsch" wrote:

I'm not sure what the error is in the formula. I've applied the resize used
range componnent but now get an error. Listed below is what now have. Did I
miss anything?

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize
UsedRange.Rows.Count - 4, 1)

Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


"Hirsch" wrote:

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize(, 1)
Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


Above is a simple macro filling down a formula from cell F5. I'm running
into a problem where it exceeds my list by 4 lines. How can I prevent this.
My goal is to have rows 1-4 blank and the fill down end at the last piece of
information.

Thanks in advance.

Duke,

that was the missing component. Thank you

"Duke Carey" wrote:

try

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5). _
Resize( activesheet.UsedRange.Rows.Count - 4, 1)

Sorry about not catching that on the first go-round

"Hirsch" wrote:

I'm not sure what the error is in the formula. I've applied the resize used
range componnent but now get an error. Listed below is what now have. Did I
miss anything?

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize
UsedRange.Rows.Count - 4, 1)

Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


"Hirsch" wrote:

Dim rngDataRange As Range

Set rngDataRange = ActiveSheet.UsedRange.Offset(4, 5).Resize(, 1)
Cells(5, 6).Copy rngDataRange.Cells(1, 1)
rngDataRange.FillDown

End Sub


Above is a simple macro filling down a formula from cell F5. I'm running
into a problem where it exceeds my list by 4 lines. How can I prevent this.
My goal is to have rows 1-4 blank and the fill down end at the last piece of
information.

Thanks in advance.