1

I have two columns of data: 

Member   Time
A        09/24/2015 09:48:36
A        09/24/2015 09:47:40
A        09/24/2015 10:11:06
A        09/24/2015 10:08:47
B        09/24/2015 10:28:45
B        09/24/2015 10:29:49
B        09/24/2015 10:30:12
B        09/24/2015 10:30:46
A        09/24/2015 10:33:59
A        09/24/2015 10:31:27

Say this data starts from A1 to B11

I have a formula to subtract consecutive time values after the column values have been sorted in ascending order: 

=(TIMEVALUE(RIGHT(B2, 8))-TIMEVALUE(RIGHT(B1, 8)))*24*60*60

The formula works fine to get me the difference in seconds. However, if I filter to get the difference only for member A, the formula considers the hidden rows as well, hence giving the wrong output.

What is the best way to apply this formula only to visible rows?

Based on an answer provided in another website, I tried writing a function as follows-

I am new to VBA so I am not able to write the code for it. 

Function MyDiff (MyRange As Range) As Integer

Dim c As Range
For Each c In MyRange
    If (c.Value = 1) And (c.EntireRow.Hidden = False) Then
        Worksheets("Sheet1").Range("B2").value - Range("B1").value, 
    End If
Next c

End Function

This is calculating the difference of the first two cells, but I cannot get it for the entire column. For demonstration purposes, I have just used cells B1 and B2 as it is. For the example data I have provided the two values have to be subtracted as Timevalue as shown in the original question.

Improve this question
5
  • You should have searched for "excel apply formula to only visible rows". Then you would have found Have a formula consider only visible rows as the second link.
    – DavidPostill ♦
    Sep 29, 2015 at 14:54
  • Thank you for the comment. I did go through that before asking this. However, the link provides the answer for the count of a range of cells. I wanted to know if there's something different when what I need is the difference between the two rows considering visible rows only.
    – Achyutha Mohan
    Sep 29, 2015 at 15:12
  • The link shows you how to write a function that use only the visible rows. You can use this kind of approach to calculate your differences.
    – DavidPostill ♦
    Sep 29, 2015 at 15:16
  • David, I am new to VBA. I have edited the question and included the function I tried which isn't working as expected. Is it worth posting this as new question?
    – Achyutha Mohan
    Sep 29, 2015 at 19:48
  • No it's fine. You have shown what you have tried. Hopefully a VBA expert will come along and help - I guess you need some kind of loop, but I wouldn't know where to start (I don't know VBA).
    – DavidPostill ♦
    Sep 29, 2015 at 20:18

0

Reset to default

You must log in to answer this question.