AutoFill going too Far Down.

[Guzzlr]

Hello All,
I have:

Sheets("HV-1 PVT").Select
Range("AA4").Select
Application.CutCopyMode = False
ActiveCell.Formula = "=AE4+AG4+AI4"
Selection.AutoFill Destination:=Range("AA4:AA" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0"

This is working
However, I would like it to AutoFill only to last entry in column AB. When I replace Range("A"& Rows.Count...) above with "AB" it still goes all the way to the bottom.
How do I make the AutoFill Stop with the last entry of AB?

Thanks for the help

 

[Joe4]

Try running this short macro and tell us what it returns:

Sub MyTest()
    Sheets("HV-1 PVT").Activate
    MsgBox Cells(Rows.Count, "AB").End(xlUp).Row)
End Sub

 

[Guzzlr]

Did you try the code I provided?
If you need it wrapped in a procedure for you (I just did a code block like you had in your original question), it would look like this:

Sub MyAutoFill()

Dim lr as Long

Sheets("HV-1 PVT").Activate

'Find last row with data in column AB
lr = Cells(Rows.Count, "AB").End(xlUp).Row

'Apply formula to entire range at once
Range("AA4:AA" & lr).FormulaR1C1 = "=RC[4]+RC[6]+RC[8]"

'Apply numer format to range
Range("AA4:AA" & lr).NumberFormat = "0"

End Sub

Also, I am not sure what that IF formula in your last post has to do with anything. It does not appear to be part of your original question.
Where does it come in?

I tried the above, and it would compute correctly, but still takes the formula all the way to the bottom. He If code is normal excel and is in a summary sheet. the code always is there, and not part of the VBA code.

 

[Guzzlr]

Try running this short macro and tell us what it returns:

Sub MyTest()
    Sheets("HV-1 PVT").Activate
    MsgBox Cells(Rows.Count, "AB").End(xlUp).Row)
End Sub

I have:

Sheets("HV-1 PVT").Activate
MsgBox Cells(Rows.Count, "AB").End(xlUp).Row

which is working and gives me a message of 50001, which is correct as I have a stop line there, so it doesn't go all the way to the bottom.

 

[Guzzlr]

I tried this from Answer Is This, and it drops all the way to row 50001, where my stop row is:

Application.ScreenUpdating = False
Sheets("HV-1 PVT").Select
Range("AA4").Select
Application.CutCopyMode = False
ActiveCell.Formula = "=AE4+AG4+AI4"
Selection.AutoFill Destination:=Range("AA4:AA" & Range("AB" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0"

 

[Joe4]

I have:

Sheets("HV-1 PVT").Activate
MsgBox Cells(Rows.Count, "AB").End(xlUp).Row

which is working and gives me a message of 50001, which is correct as I have a stop line there, so it doesn't go all the way to the bottom.

OK, then the code I posted should put the formula in cells AA4:AA50001.
If it is not doing that, I suspect that maybe you have other codes interfering with it or changing it after the fact.

One other possibility. What is the name of the VBA Module that you are trying to run this code from?

 

[Guzzlr]

OK, then the code I posted should put the formula in cells AA4:AA50001.
If it is not doing that, I suspect that maybe you have other codes interfering with it or changing it after the fact.

One other possibility. What is the name of the VBA Module that you are trying to run this code from?

the module name is: Run_HV1()
is it possible to make it end at the last entry of AB?
Thanks

 

[Joe4]

the module name is: Run_HV1()

That looks like a procedure name, not a module name.
I need to know which Module you have placed the code in.
This shows you what a module is: Module (Insert, Remove, Restore, and BackUp) | Excel VBA Tutorial

is it possible to make it end at the last entry of AB?

That is precisely what this part of my code already does:

Cells(Rows.Count, "AB").End(xlUp).Row

 

[Guzzlr]

That looks like a procedure name, not a module name.
I need to know which Module you have placed the code in.
This shows you what a module is: Module (Insert, Remove, Restore, and BackUp) | Excel VBA Tutorial


That is precisely what this part of my code already does:

Cells(Rows.Count, "AB").End(xlUp).Row

Module Name is: K_RunHV1

 

[Joe4]

OK, it makes no sense why it wouldn't work.
You already confirmed it is going down to line 50001.

Without actually having access to your file to see what is actually going on, I really don't have any other ideas on why it would not be working.
I have a feeling that there may be some else interfering with it or some important detail that is being left out.

 

[Guzzlr]

OK, it makes no sense why it wouldn't work.
You already confirmed it is going down to line 50001.

Without actually having access to your file to see what is actually going on, I really don't have any other ideas on why it would not be working.
I have a feeling that there may be some else interfering with it or some important detail that is being left out.

Well, I cheated...
This is working:

Sheets("HV-1 PVT").Activate
With Range("AA4:AA" & Cells(Rows.Count, "AB").End(xlUp).Row)
    .Formula = "=IF(AB4<>"""",AE4+AG4+AI4,"""")"
    .NumberFormat = "0"
End With

Thanks for all the help