Macro Autofill formula and keep values only

[opazzo]

Hi there,

I have a formula in A6 that I need to copy down to the last row in column A. Once copied I only need to keep values in all cells except in A6.

Can I use :

Range("A6").AutoFill Destination:=Range("A6:A" & LastRow), Type:=xlFillValues

Instead of my current code :

Range("A6").AutoFill Destination:=Range("A6:A" & LastRow)
Range("A7:AJ" & LastRow) = Range("A7:AJ" & LastRow).Value

If not do you see any other way to improve speed of this code ( I am using the same on many different columns and it takes a good 2 or 3 seconds to execute).

Thanks

 

[TMShucks]

What's the formula and/or rest of the code look like?

 

[opazzo]

The formula is =IF(D6="","",COUNTIF($A$6:$A$65534,AK6)>1)

And the rest of the code is like that, with different formulas in row 6:

---
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("L6:M6").AutoFill Destination:=Range("L6:M" & LastRow)
Range("L7:M" & LastRow) = Range("L7:N" & LastRow).Value

Range("N6:N6").AutoFill Destination:=Range("N6:N" & LastRow)
Range("N7:N" & LastRow).Copy
Range("N:N" & LastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

Range("AA6:AD6").AutoFill Destination:=Range("AA6:AD" & LastRow)
Range("AA7:AD" & LastRow).Copy
Range("AA7:AD" & LastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

Range("AE6:AE" & LastRow) = Range("AD6:AD" & LastRow).Value

Range("AE6:AE" & LastRow) = Range("AE6:AE" & LastRow).Value
Range("AE4").Copy
Range("AE6:AE" & LastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
Application.CutCopyMode = False

Range("AF6:AH6").AutoFill Destination:=Range("AF6:AH" & LastRow)
Range("AF7:AH" & LastRow) = Range("AF7:AH" & LastRow).Value

Range("S6:V6").AutoFill Destination:=Range("S6:V" & LastRow)
Range("S7:V" & LastRow) = Range("S7:V" & LastRow).Value

Range("H6:H" & LastRow) = Range("H6:H" & LastRow).Value

Range("AJ6:AK6").AutoFill Destination:=Range("AJ6:AK" & LastRow)
Range("AJ7:AK" & LastRow) = Range("AJ7:AK" & LastRow).Value---
---

I use screen update off but still the execution is not as fast as I would like.

 

[TMShucks]

I'm a little confused as you said you wanted to put the formula in column A from cell A6. But the formula refers to cell A6: =IF(D6="","",COUNTIF($A$6:$A$65534,AK6)>1).

I'm also a little unhappy/nervous about the code:

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

You want to copy the formula down "to the last row in column A" ... but you're using column A to determine the last row. So, does column A have values or formulae in it to start with? And you want to overwrite it? But, if you're putting a formula into column A, that refers to column A, you'd get a circular reference.

Am I missing or misinterpreting something here?

The way I'd generally input formulae and convert to values is:

[COLOR=#00007f][COLOR=#00007f]Dim[/COLOR] lLR [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Long[/COLOR]
[COLOR=#007f00]'lLR = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=#007f00]'(need to pick a "full" column)[/COLOR]
lLR = 100   [COLOR=#007f00]' for testing[/COLOR]
 
[COLOR=#00007f]With[/COLOR] Range("L6:L" & lLR)
    .Formula = "=IF(D6="""","""",COUNTIF($A$6:$A$65534,AK6)>1)"
    .Value = .Value
[COLOR=#00007f]End[/COLOR] [COLOR=#00007f]With[/COLOR]
[/COLOR]

 

[opazzo]

Indeed, I got confused with the ranges in my example. Sorry for that. Here is the correct values for the records.

This is the formula in AI6 :

=IF(D6="","",COUNTIF($AK$6:$AK$65535,AK6)>1)

My Macro is as follows :

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("AI6").AutoFill Destination:=Range("AI6:AI" & LastRow)
Range("AI7:AI" & LastRow) = Range("AI7:AI" & LastRow).Value

I will use your recommendation to embed the formula in the code and to use "With Range". I'll post some feedback on the speed improvement.

Thanks