That's perfect Dave. Many thanks!

I do have another question. The function
=If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) works perfect although I would like
to also return the value in column C. In other words, if there is a match
for column B to Column A then I would like to copy and paste the value from
Column C from the row where columns A and B match.

Can the Offset function be used?

Any help you can provide
would be greatly appreciated!!

"Dave Peterson" wrote:

Activesheet.autofiltermode = false
will remove the arrows (and show all the data)

To show all the data, but keep the arrows.

with activesheet
If .FilterMode Then
.ShowAllData
End If
End if

Stan wrote:

I can certainly work with that. Many thanks!

Oh, and by the way once auto filter is turn on, do you know the code to turn
it off? Selection.AutoFilter.Off is working but giving me an error.

Range("B2:B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="7760"
Selection.AutoFilter.Off

"akphidelt" wrote:

Hey stan, not sure if this helps you or not... but using an alternative
instead of VB code, which would be a much more complicated method then this.
But say you hide column B or put the data in Column C and hide Column C. In
column C put the formula

=If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1)

Copy this formula and drag down to the end of your data... make sure you
change B6 to wherever the data ends and to make sure to keep the $ signs.

This formula will leave any value that does not match blank, and if the
value exists it will fill in the value. So you will get your desired
results... im just not sure if this is how you wanted to get them.

"Stan" wrote:

I have 15 minute interval data in column A and through vb code I need to
select the data in column B and have it moved to match the cooresponding row
in column A. In other words, if there isn't an interval of 6:15 in column B
then I want to leave that row blank. In the example below, the interval in
Column B row 2 should be moved to row 3 since it matches what's in column A.

Any help you can provide would be greatly appreciated! Many thanks!

Column A Column B Desired Results for Column B
1) 6:00 6:00 6:00
2) 6:15 6:30
3) 6:30 6:45 6:30
4) 6:45 7:15 6:45
5) 7:00
6) 7:15 7:15
......
11:30

--

Dave Peterson

=Offset() could be used, but =index() (non-volatile) makes more sense here.

Stan wrote:

That's perfect Dave. Many thanks!

I do have another question. The function
=If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1) works perfect although I would like
to also return the value in column C. In other words, if there is a match
for column B to Column A then I would like to copy and paste the value from
Column C from the row where columns A and B match.

Can the Offset function be used?

Any help you can provide
would be greatly appreciated!!

"Dave Peterson" wrote:

Activesheet.autofiltermode = false
will remove the arrows (and show all the data)

To show all the data, but keep the arrows.

with activesheet
If .FilterMode Then
.ShowAllData
End If
End if

Stan wrote:

I can certainly work with that. Many thanks!

Oh, and by the way once auto filter is turn on, do you know the code to turn
it off? Selection.AutoFilter.Off is working but giving me an error.

Range("B2:B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="7760"
Selection.AutoFilter.Off

"akphidelt" wrote:

Hey stan, not sure if this helps you or not... but using an alternative
instead of VB code, which would be a much more complicated method then this.
But say you hide column B or put the data in Column C and hide Column C. In
column C put the formula

=If(Iserror(Match(A1,$B$1:$B$6,0)),"",A1)

Copy this formula and drag down to the end of your data... make sure you
change B6 to wherever the data ends and to make sure to keep the $ signs.

This formula will leave any value that does not match blank, and if the
value exists it will fill in the value. So you will get your desired
results... im just not sure if this is how you wanted to get them.

"Stan" wrote:

I have 15 minute interval data in column A and through vb code I need to
select the data in column B and have it moved to match the cooresponding row
in column A. In other words, if there isn't an interval of 6:15 in column B
then I want to leave that row blank. In the example below, the interval in
Column B row 2 should be moved to row 3 since it matches what's in column A.

Any help you can provide would be greatly appreciated! Many thanks!

Column A Column B Desired Results for Column B
1) 6:00 6:00 6:00
2) 6:15 6:30
3) 6:30 6:45 6:30
4) 6:45 7:15 6:45
5) 7:00
6) 7:15 7:15
......
11:30

--

Dave Peterson

--

Dave Peterson