calculate month from date

[robertvdb]

I have a sheet where Col A lists (thousands) of dates, and in Col C the respective months are calculated.

I do this with the below code, however this takes a few second (over 100K dates).

Any way to speed this up ?

Thanks.

Private Sub Worksheet_Activate()
Dim i As Long
Dim nRow As Long

nRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To nRow
    Range("C" & i).Formula = "=TEXT(A" & i & ",""yyyy/mm"")"
Next i


End Sub

 

[Phuoc]

Try thí

Private Sub Worksheet_Activate()
Dim nRow As Long

nRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & nRow).Value = "=TEXT(RC[-2],""yyyy/mm"")"

End Sub

 

[John_w]

Another one:

Private Sub Worksheet_Activate()
    Range("C2").Formula = "=TEXT(A2,""yyyy/mm"")"
    Range("C2").AutoFill Destination:=Range("C2:C" & Cells(Rows.Count, 1).End(xlUp).Row), Type:=xlFillDefault
End Sub

 

[robertvdb]

Thanks John, works fine. Thanks also to Phuoc. I will also keep your suggested solution on my records.