What's a more effective way to write this code?

[Macro_Nerd99]

What's a more effective way to write this code so I don't have to insert text into cells and then delete it?
Also, how would I display the total hours worked in 40:00:00 format, instead of a 40.0 decimal format?
Any other advice/feedback is appreciated.

Public Sub Calculate_Hours()
Dim StartTime as date
dim EndTime as date
dim i as integer
dim lr as long
dim fr as long
dim total_hours as long


With thisworkbook.activesheet.autofilter.Range
fr = Range("B" & .offset(1,0).specialcells(xlcelltypevisible)(1).row).row
lr = cells(Rows.Count,"C").end(xlUp).row
end with

for i = fr to lr
StartTime = ws.range("B" & i).value
EndTime = ws.Range("C".value
ws.range("H" & i).value = datediff("n", starttime, EndTime)/60
Next i

Total_hours = application.worksheetfunction.sum(Range("H" & fr & ":H" & lr))
Filter_Form.hours_tb.text= Total_hours
Range("H" & fr & ":h" & lr).clearcontents

End Sub

 

[offthelip]

change your code like this:

total_hours = 0
For i = fr To lr
starttime = ws.Range("B" & i).Value
EndTime = ws.Range("C".value
total_hours = total_hours + DateDiff("n", starttime, EndTime) / 60
Next i

Filter_Form.hours_tb.Text = total_hours

 

[Mario_R]

Hi.
The code you show never worked because in:

ws.Range("C".value

it is missing the row and a parenthesis.
___________________________

On the other hand, it is not clear if you want to apply this operation to all the rows of the range or only to the visible cells of the filtered range.

 

[kevin9999]

What filter are you applying before you run your code?