Time diffrence between two date stamps, faulty VBA

[Martu Theophilus]

Dear Experts,

Could you please help me to repair my macro in VBA. The aim is to put in a column R the time difference in days ( can also show hours) between later date stamp column M and the first date stamp in column B. The macro attached below works and calculates the difference in days, however it runs the macro till the end of the column R. returning the value of 0, even when columns B and M are empty. I would like the calculation only to be done when there is a value in the time stamp cells in column B and M.
I would be grateful for yours suggestions.

Kind regards

Martek

Sub datediff()


With Range("R12:R" & Range("B" & Rows.Count).End(xlDown).Row)

.FormulaR1C1 = "=datedif(rc[-16],rc[-5],""d"")"

Dim x As Integer

      Application.ScreenUpdating = False

      ' Set numrows = number of rows of data.

           ' Establish "For" loop to loop "numrows" number of times.

               ' Insert your code here.

         ' Selects cell down 1 row from active cell.

        

      End With

End Sub

I have tried with .End(xlUp) however the loop is stopped and column r does not show any data -

the time difference calculation does not work anymore....... Any ideas which formula is the best to simply calculate the difference between two date stamps.

Thank you
M

 

[Martu Theophilus]

Tracking example.xlsm


Dear Joe,

I am not sure if everything is correct. I have attached the file via dropbox. In my file I have 3 modules and 1 project in which I paste all important macro.
I would like to have the easiest possible result in column R for the difference of column m - column b - so the macro should only run when there is an entry in the cells of column b and column m. I dont quite understand why the file is so big already, so anything simple and smaller is great.
Once again, many thanks
MM

 

[Joe4]

The day got away from me, and I did not get to play around with it as much as I would have liked to yet, but I do have an answer for the reason why it is so big. On your "ORDER TRACKING" sheet, it thinks the end of your data is the last line in the workbook (row 1048576). This sometimes happen if you format or apply conditional formatting or data validation to every row on your sheet. You can see this by hitting CTRL+END. This takes you to where Excel thinks is your last row on that sheet (when really, it should be row 1325).

So if you run this macro to delete all those bogus extra rows, you will see your size decrease dramatically.

Sub MyDeleteRows()
    Application.EnableEvents = False
    Sheets("Order Tracking").Activate
    Rows("1326:1048576").Delete
    Application.EnableEvents = True
    ActiveWorkbook.Save
End Sub

I will see if I can find some more time to play around with your workbook tomorrow.

 

[Martu Theophilus]

I was trying to clear the formatting without successes. You are a genius, it works and the size is reduced to 626kb. Thank you soooooo much. MM

 

[Joe4]

You are welcome.
So now are all your problems solved, or do you still have any issues raised in this thread going on.

 

[Martu Theophilus]

Dear Joe,

It is perfect. Thank you . Now with help of an expert like you, I can design more macro files
Thank you in the loop endlessly

 

[Joe4]

Excellent!
Feel free to post any new questions you may have to this Board!