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

Dim lr as Long

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

 

[My Aswer Is This]

Try this:

Sub Auto_Fill()
'Modified 4/21/2022  4:30:22 PM  EDT
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"
End Sub

 

[Joe4]

Try this:
Sub Auto_Fill()
'Modified 4/21/2022 4:30:22 PM EDT
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"
End Sub

Get rid of all those "Select" and "Selection" parts of the code!
They are unnecessary and actually slow the code down (they are usually just the result of recorded code, which is very literal and can be cleaned up).

You really only need a few lines of code, like I showed in my post (half of the lines in my code are just comments to explain what is going on).

We can simplify it even further to this:

Sub MyAutoFill()
    With Range("AA4:AA" & Cells(Rows.Count, "AB").End(xlUp).Row)
        .FormulaR1C1 = "=RC[4]+RC[6]+RC[8]"
        .NumberFormat = "0"
    End With
End Sub

 

[My Aswer Is This]

Get rid of all those "Select" and "Selection" parts of the code!
They are unnecessary and actually slow the code down (they are usually just the result of recorded code, which is very literal and can be cleaned up).

You really only need a few lines of code, like I showed in my post (half of the lines in my code are just comments to explain what is going on).

We can simplify it even further to this:

Sub MyAutoFill()
    With Range("AA4:AA" & Cells(Rows.Count, "AB").End(xlUp).Row)
        .FormulaR1C1 = "=RC[4]+RC[6]+RC[8]"
        .NumberFormat = "0"
    End With
End Sub

Yes I know that. I just modified the code he had. I never use select like he had here. But

 

[Guzzlr]

Yes I know that. I just modified the code he had. I never use select like he had here. But

I've always had problems getting rid of the selects...I continually get error messages, or the code skips right over it and doesn't have an output, so I'm obviously doing something wrong.

 

[Joe4]

I've always had problems getting rid of the selects...I continually get error messages, or the code skips right over it and doesn't have an output, so I'm obviously doing something wrong.

Typically, if you have one line that ends in "Select", and the next line begins with "ActiveCell" or "Selection", you can combine those two rows together, i.e.
if you have recorded something like this:

    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"

you can combine them together like this:

    Range("F1").FormulaR1C1 = "=RC[-2]+RC[-1]"

This is because it usually isn't necessary to select ranges in order to work with them (the Macro Recorder is very literal, and records all cell selection, screen scrolls, etc).
Not only does removing them make your code shorter, it also speeds it up.
The performance improvement may not be that noticeable on just a few cells, but if you are selecting cells within a loop that goes through a lot of data, the difference can be quite significant.

And its just good programming practice (and a little more professional).

 

[Guzzlr]

Try this:

Sub Auto_Fill()
'Modified 4/21/2022  4:30:22 PM  EDT
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"
End Sub

The code above looks like what I tried by placing "AB" instead of "A", and it still went to the bottom.
AB does have Text in it:

=IF(AC89<>"",TEXT($AC89,"ddd"),"")

I'll try My Answer Is This in a minute and report back.
Thanks for the help

 

[Joe4]

The code above looks like what I tried by placing "AB" instead of "A", and it still went to the bottom.
AB does have Text in it:

=IF(AC89<>"",TEXT($AC89,"ddd"),"")

I'll try My Answer Is This in a minute and report back.
Thanks for the help

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?

 

[Guzzlr]

So I tried this:

With Range("AA4:AA" & Cells(Rows.Count, "AB").End(xlUp).Row)
        .FormulaR1C1 = "=RC[4]+RC[6]+RC[8]"
        .NumberFormat = "0"
    End With

And it would fill not fill any cells in column AA.

I'll try Joe4 now and report back