automatically calculate the time from 2 separate cells with variable rows inbetween

[mingandmong]

Hi
I'm using excel 2013
and require a macro to calculate my sheet with 2000 rows of data

I have a start time in column G row 2 and a finish time in column J row 7 and wish to calculate in Column O row 7

The data then has an empty row before starting on the next block of data

The start time is now in row 9 and the finish time is in row 14 and wish to calculate in Column O row 14

The source data is always in G/J with the result in column O all through the data sheet the blocks of data have variable rows

Many thanks

 

[The_Macrotect]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

I'm sure it can be done, but I'd need more info. Can you post your sheet?

Matt

 

[Peter_SSs]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

Is there anything else in column J besides the finish time for each block?

If so, I'd like to know more abot what is in each data block (eg G2:J6, G9:J14 etc)

 

[mingandmong]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

Hi Peter yes

not sure how to upload the code but its here

https://ibb.co/CVSJj6V

https://ibb.co/CVSJj6V

 

[mingandmong]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

Hi _Macrotect
I have included a link.. not sure how to imbed it
I have a new sheet every day and the rows change dependent on the data provided hope it helps to give you a general idea

 

[mingandmong]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

 

[Fluff]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

How about

Sub mingandmong()
   Dim Rng As Range
   
   For Each Rng In Range("G:G").SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count - 1, 8).Resize(1, 1).Value = _
         Rng.Offset(Rng.Count - 1, 3).Resize(1, 1) - Rng.Resize(1, 1)
   Next Rng
End Sub

 

[mingandmong]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

Thank you Fluff

works perfect

 

[Fluff]

Re: automaticaly calculate the time from 2 spertae cells with variable rows inbetween

You're welcome & thanks for the feedback