Autofill destination VBA

[wenhwei17]

Hello all!

i have this formula for autofill formula typed in cell V9

Selection.autofill destination:=range(“v9:v” & range (“u” & rows.count).end(x1up).row)

however if there is only one row then it will have error. Need help on this thanks!

 

[maabadi]

I think You Have Typo at Xlup not X1up ( it is lower L not 1) And don't need select it

Range("V9").autofill destination:=range(“v9:v” & range (“u” & rows.count).end(xlup).row)

 

[wenhwei17]

hello! I have changed it but it still says autofill method of range class failed hmm

I think You Have Typo at Xlup not X1up ( it is lower L not 1) And don't need select it

Range("V9").autofill destination:=range(“v9:v” & range (“u” & rows.count).end(xlup).row)

)

 

[maabadi]

Are you change ranges. your destination range must include also source range.
Also Change “ With "
Try This:

Range("V9").AutoFill Destination:=Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)

 

[wenhwei17]

Are you change ranges. your destination range must include also source range.
Also Change “ With "
Try This:

Range("V9").AutoFill Destination:=Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)

Hello thanks for your reply. However i dont quite understand. Maybe i show u the full vba:

Range("C9").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.copy

Range("U9").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("V9").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",RC[-1])"

Range("V9").Select



Selection.AutoFill Destination:=Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)

Range(Selection, Selection.End(xlDown)).Select



Selection.copy

Range("C9").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Columns("U:V").Select

Application.CutCopyMode = False

Selection.ClearContents

 

[maabadi]

Try this:

Sub DataValid1()
Dim Lr As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
Range("C9:C" & Lr).Copy
Range("U9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Application.CutCopyMode = False
Range("V9").FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",RC[-1])"
Range("V9").AutoFill Destination:=Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)
Range("V9:V" & Lr).Copy
Range("C9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("U:V").ClearContents
'Application.CutCopyMode = False
End Sub

 

[wenhwei17]

Try this:

Sub DataValid1()
Dim Lr As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
Range("C9:C" & Lr).Copy
Range("U9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Application.CutCopyMode = False
Range("V9").FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",RC[-1])"
Range("V9").AutoFill Destination:=Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)
Range("V9:V" & Lr).Copy
Range("C9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("U:V").ClearContents
'Application.CutCopyMode = False
End Sub

I still got stuck here

Sub DataValid1()
Dim Lr As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
Range("C9:C" & Lr).Copy
Range("U9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Application.CutCopyMode = False
Range("V9").FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",RC[-1])"
Range("V9").AutoFill Destination:=Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)
Range("V9:V" & Lr).Copy
Range("C9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("U:V").ClearContents
'Application.CutCopyMode = False
End Sub

 

[maabadi]

Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[MARK858]

@wenhwei17 you can just apply the formula directly to the range without autofilling.

    With Range("V9:V" & Range("U" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",RC[-1])"
        .Value = .Value
    End With

 

[footoo]

Or change column C directly (without using columns U:V) :

Sub DataValid1()
Dim r$: r = Range("C9:C" & Cells(Rows.Count, 3).End(3).Row).Address
Range(r) = Evaluate("IF(" & r & "=" & Range(r).Offset(-1).Address & ",""""," & r & ")")
End Sub