Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Macro and sub rountines | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |