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!

 

[wenhwei17]

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

Hello this works for me, thanks!! Could you also assist me on this:

in regards to the data in column C i would like to create numbering to it:

my code below, however same thing happen if there is only one row. Much thanks

Range("B9").Select

ActiveCell.FormulaR1C1 = "=IF(RC[1]<>"""",COUNTA(R9C3:RC3)-COUNTBLANK(R9C3:RC3),"""")"

Range("B9").AutoFill Range("B9:B" & Range("C" & Rows.Count).End(xlUp).Row)

 

[footoo]

Hello this works for me, thanks!! Could you also assist me on this:

in regards to the data in column C i would like to create numbering to it:

my code below, however same thing happen if there is only one row. Much thanks

Range("B9").Select

ActiveCell.FormulaR1C1 = "=IF(RC[1]<>"""",COUNTA(R9C3:RC3)-COUNTBLANK(R9C3:RC3),"""")"

Range("B9").AutoFill Range("B9:B" & Range("C" & Rows.Count).End(xlUp).Row)

Are you sure the formula does what you want?
Post some sample data of how to number column B.

 

[wenhwei17]

Hello @footoo , thanks for yr help. As picture attached (top - raw data) (bottom - outcome) - hope its clearer now

- remove duplicates
- numbering

Concern: Variable rows, sometimes there will be only one row

 

[footoo]

Maybe this :

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 & ")")
[B9] = 1
Range(r).Offset(1, -1).Formula = "=IF(C10="""","""",IF(C9="""",1,B9+1))"
End Sub

Or this :

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 & ")")
Range(r)Offset(0, -1).Formula = "=IF(C9<>"""",counta($C$9:$C9),""""))"
End Sub

 

[footoo]

Correction to second macro :

Range(r)Offset(0, -1).Formula = "=IF(C9<>"""",counta($C$9:$C9),"""")"

 

[wenhwei17]

Hihi, why when i change to this in macro it will highlight in red? The first macro came back with 1 and then 1 as attached

Correction to second macro :

Range(r)Offset(0, -1).Formula = "=IF(C9<>"""",counta($C$9:$C9),"""")"

 

[footoo]

Range(r).Offset(0, -1).Formula = "=IF(C9<>"""",counta($C$9:$C9),"""")"

 

[wenhwei17]

Range(r).Offset(0, -1).Formula = "=IF(C9<>"""",counta($C$9:$C9),"""")"

Thanks! Working great