Excel Vba .value same as .Copy .PasteSpecial xlPasteValues

[Biz]

Hi,

I always thought .value=.value was same as .copy .PasteSpecial xlPasteValues but I found it's not same when a formula is used which returns "".

 'Assessment# Dup
    With Range("AA2:AA" & LastRow)
    '"=IF(COUNTIF(R2C20:R30591C20,RC[-1])>1,1,"""")"
        .Formula = "=IF(COUNTIF($Z$2:$Z$" & LastRow & ",Z2)>1,1,"""")"
'        .Value = .Value
        
        .Copy
        .PasteSpecial xlPasteValues
'        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'        :=False, Transpose:=False
    Application.CutCopyMode = False
    End With

Is there a better way to solve this problem?

Your help would be greatly appreciated.

Kind Regards

Biz

 

[Scott Huish]

I believe the only only difference in this case is that you will not end up with the value of an empty string, you will end up with a blank cell.
How does this affect what you are trying to do?

 

[Biz]

Hi Scott,

This affects my sort ordeer as 1 indicates, items to deleted and "" items to ignore. I use results to sort data and then delete items based on code below.

'Sort by Delete
    Set aSheet = Sheets("Report")
    
    'Add Sort Keys
    With aSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("AD2:AD" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End With
    
    'Sort to Delete
    With aSheet.Sort
        .SetRange Range("A1:AD" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    aSheet.Range("AD2", Range("AD1").end(xlDown)).SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete

Kind Regards

Biz