Selection.AutoFill Destination:=Range Question

[drozek]

I recorded a macro to do an autofill, the first time it ran it only had 100 rows, but the rows count can change. How would I make this macro work depends on how many rows there are?

Column A will always have the correct number of rows.

Selection.AutoFill Destination:=Range("N4:N100")
Range("N4:N100").Select

 

[kweaver]

In your macro, determine the number of rows using something like:

LR = Cells(Rows.Count,"A"),End(xlUp).Row

Then, in your range:

Range("N4:N" & LR)

 

[Joe4]

You can find the last row in column M with data like this:

Dim lr as Long
lr = Cells(Rows.Count, "M").End(xlUp).Row

Then you can just use:

Range("N4:N" & lr)...

Note that if the lines before this code are putting the initial formula in cell N4, you do not need to use Autofill at all. You can assign the formula to the whole range directly at once.
If you need help doing that, posting the code that populates the formula.

 

[drozek]

You can find the last row in column M with data like this:

Dim lr as Long
lr = Cells(Rows.Count, "M").End(xlUp).Row

Then you can just use:

Range("N4:N" & lr)...

Note that if the lines before this code are putting the initial formula in cell N4, you do not need to use Autofill at all. You can assign the formula to the whole range directly at once.
If you need help doing that, posting the code that populates the formula.

So it does the initial formula

here is the code:

Range("N4").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-9]"
Range("M4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-9]"
Range("N4").Select
Selection.AutoFill Destination:=Range("N4:N100")
Range("N4:N100").Select
Range("M4").Select
Selection.AutoFill Destination:=Range("M4:M100")
Range("M4:M100").Select
Range("K3").Select

 

[Joe4]

OK, first you need to determine which column has data in it, that you want to use to find your last row. I thought it might be "M", since you are autofilling "N", but it looks like you are autofilling "M" too.
Let's say it is column K (if not, just change the "K" in the last row calculation to the correct column letter). Then you could replace all your above code with this:

Dim lr as Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
Range("M4:M" & lr)[COLOR=#574123].FormulaR1C1 = "=RC[-1]-RC[-9]"[/COLOR]
Range("N4:N" & lr)[COLOR=#574123].FormulaR1C1 = "=RC[-2]-RC[-9]"[/COLOR]

 

[drozek]

OK, first you need to determine which column has data in it, that you want to use to find your last row. I thought it might be "M", since you are autofilling "N", but it looks like you are autofilling "M" too.
Let's say it is column K (if not, just change the "K" in the last row calculation to the correct column letter). Then you could replace all your above code with this:

Dim lr as Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
Range("M4:M" & lr)[COLOR=#574123].FormulaR1C1 = "=RC[-1]-RC[-9]"[/COLOR]
Range("N4:N" & lr)[COLOR=#574123].FormulaR1C1 = "=RC[-2]-RC[-9]"[/COLOR]

can you help me understand dim lr as long?

I am guessing it is a range?

The only reason I am asking is because I have a this code in the same macro:

Dim lr As Long
lr = Sheets("Data").Range("D" & Sheets("Data").Rows.Count).End(xlUp).Row
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & lr & "C13", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="byCustomer", DefaultVersion:=6

 

[Joe4]

"lr" is just the variable I chose for our "last row" calculation. If you are already using "lr" elsewhere, choose a different variable name.

The "Dim" statement is declaring the variable to be an Integer. You should declare all your variables before using them. It helps avoid errors, and if you use it in conjunction with "Option Explicit", you can force yourself to declare all variables before using them. This is a great way to help prevent typos in your variables.

See: https://www.excel-easy.com/vba/examples/option-explicit.html

 

[drozek]

"lr" is just the variable I chose for our "last row" calculation. If you are already using "lr" elsewhere, choose a different variable name.

The "Dim" statement is declaring the variable to be an Integer. You should declare all your variables before using them. It helps avoid errors, and if you use it in conjunction with "Option Explicit", you can force yourself to declare all variables before using them. This is a great way to help prevent typos in your variables.

See: https://www.excel-easy.com/vba/examples/option-explicit.html

Thanks I figured as much! I appreciate it

 

[Joe4]

You are welcome.