Hello...I need a macro to autofill a formula as long as there is data in Col
A. My problem is that I have totals data in Row 51 so I need the autofill go
no farther down than row 50. The first cell to have data is A4. The last row
in Col A to have data varies but always ends somewhere before A51. The
formulas to be autofilled are in B4:I4. The current macro below fills all the
way down to A51 because totals data resides in A51 which is incorrect for my
needs. The macro I have now is below. How can it be changed so it doesn't
look all the way to the end of the WS for data in Col A and instead fills no
farther than row 50? Thanks! Joe M.

Sub Extend_Form1()
'
Sheets("Hours").Select
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
..Range("B4:I" & lRow).FillDown
End With
'
End Sub

Hi

This should do it:

lRow = .Range("A51").End(xlUp).Row

Regards,
Per

On 10 Feb., 18:23, Joe M. wrote:
Hello...I need a macro to autofill a formula as long as there is data in Col
A. My problem is that I have totals data in Row 51 so I need the autofill go
no farther down than row 50. The first cell to have data is A4. The last row
in Col A to have data varies but always ends somewhere before A51. The
formulas to be autofilled are in B4:I4. The current macro below fills all the
way down to A51 because totals data resides in A51 which is incorrect for my
needs. The macro I have now is below. How can it be changed so it doesn't
look all the way to the end of the WS for data in Col A and instead fills no
farther than row 50? Thanks! Joe M.

Sub Extend_Form1()
'
Sheets("Hours").Select
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B4:I" & lRow).FillDown
End With
'
End Sub

Use this line instead:

lRow = .Range("A51").End(xlUp).Row

This will return the last row in column A with data that is above row 51.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Joe M." wrote:

Hello...I need a macro to autofill a formula as long as there is data in Col
A. My problem is that I have totals data in Row 51 so I need the autofill go
no farther down than row 50. The first cell to have data is A4. The last row
in Col A to have data varies but always ends somewhere before A51. The
formulas to be autofilled are in B4:I4. The current macro below fills all the
way down to A51 because totals data resides in A51 which is incorrect for my
needs. The macro I have now is below. How can it be changed so it doesn't
look all the way to the end of the WS for data in Col A and instead fills no
farther than row 50? Thanks! Joe M.

Sub Extend_Form1()
'
Sheets("Hours").Select
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B4:I" & lRow).FillDown
End With
'
End Sub

Thanks very much!

"Per Jessen" wrote:

Hi

This should do it:

lRow = .Range("A51").End(xlUp).Row

Regards,
Per

On 10 Feb., 18:23, Joe M. wrote:
Hello...I need a macro to autofill a formula as long as there is data in Col
A. My problem is that I have totals data in Row 51 so I need the autofill go
no farther down than row 50. The first cell to have data is A4. The last row
in Col A to have data varies but always ends somewhere before A51. The
formulas to be autofilled are in B4:I4. The current macro below fills all the
way down to A51 because totals data resides in A51 which is incorrect for my
needs. The macro I have now is below. How can it be changed so it doesn't
look all the way to the end of the WS for data in Col A and instead fills no
farther than row 50? Thanks! Joe M.

Sub Extend_Form1()
'
Sheets("Hours").Select
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B4:I" & lRow).FillDown
End With
'
End Sub

.

Thank you!

"Luke M" wrote:

Use this line instead:

lRow = .Range("A51").End(xlUp).Row

This will return the last row in column A with data that is above row 51.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Joe M." wrote:

Hello...I need a macro to autofill a formula as long as there is data in Col
A. My problem is that I have totals data in Row 51 so I need the autofill go
no farther down than row 50. The first cell to have data is A4. The last row
in Col A to have data varies but always ends somewhere before A51. The
formulas to be autofilled are in B4:I4. The current macro below fills all the
way down to A51 because totals data resides in A51 which is incorrect for my
needs. The macro I have now is below. How can it be changed so it doesn't
look all the way to the end of the WS for data in Col A and instead fills no
farther than row 50? Thanks! Joe M.

Sub Extend_Form1()
'
Sheets("Hours").Select
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B4:I" & lRow).FillDown
End With
'
End Sub