vba script for date difference between two columns

[abhi_jain80]

Hi folks,

I need to calculate the date difference between the two columns A and B and store the value in C column. There are around 100k rows and some missing dates in the B column. Here is the vba code I am using...it is absolutely working fine what I need but taking around 15 mins to run on my machine. Just thinking, if there is any better optimized way to do the same? Thanks in advance...

>>> use code - tags <<<

Sub DateDifference()

Dim FD As Date
Dim LD As Date
Dim LR As Long

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

For i = 2 To LR

FD = Range("A" & i).Value
LD = Range("B" & i).Value

If Range("B" & i).Value = "" Then
Range("C" & i).Value = ""
Else
If DateDiff("d", FD, LD) <= 0 Then
Range("C" & i).Value = 1
Else
Range("C" & i).Value = DateDiff("d", FD, LD)

End If
End If
Next i

End Sub

 

[Marc L]

Hi,​

​

according to any Excel forum rules you forgot the link(s) of other forum(s) where you already have created the same thread !​

And use the 3 dots icon when posting any code here …​

​

 

[abhi_jain80]

Hi Marc,
I have not created the same thread in any other forum, thanks

 

[Marc L]

Ok, thanks.​

​

Did you try at least a formula which is often faster than any bad looping cell-by-cell code ?​

​

 

[abhi_jain80]

As per your suggestion...now I have removed the vba script and instead using the formula, much faster. Thanks