Filter, Sort, Macro.. what to do?

Z

zingle

New member
Joined
Oct 16, 2015
Messages
5
Reaction score
0
Points
0
Hi, i'm trying to select and copy only certain rows of data. I've tried filtering but can't get it to work due to the date and time both being in one cell and wanting a range.

Basically I have a large amount of data with the date and time in the first column and then various things in the columns after it.

I want to have say only only the rows which have morning data between 10:00 and 12:00.

It doesn't matter if it just copies to rows bellow the original data, copies it to a new sheet or just deletes the rows I don't want as I will just be copy and pasting it into another application.

If I try sorting or filtering I run into problems with the date and time both being in the same cell or looking for a specific value in a cell when i'm after a range of values and I don't have a clue about macros or anything else.
 

Attachments

  • Cs9YZAF.jpg
    Cs9YZAF.jpg
    16.9 KB · Views: 10
  • 8j0d3xe.jpg
    8j0d3xe.jpg
    5.8 KB · Views: 10
How about adding a helper column for the hour portion of the time?
=MOD(A2,1) formatted as Time.

Then a macro to filter on the new column, something like this
Code: 
Sub Filter_For_Time()
    Dim starttime As Date
    Dim endtime As Date
    
starttime = #10:00:00 AM#
endtime = #12:00:00 PM#

With Sheets("Sheet1")
    .AutoFilterMode = False
    With .UsedRange
        .AutoFilter Field:=4, _
                    Criteria1:=">=" & starttime, _
                    Operator:=xlAnd, _
                    Criteria2:="<=" & endtime
    End With
End With

End Sub
 
Thanks but using MOD(A2,1) just returns 0/01/1900.

Just doing =A2 and formatting the column to time is giving me a column with just time but running that macro is just hiding everything except the first row.

I also tried to a custom between filter but it does the same thing and just hides everything but the first row now matter what greater than and less than values I use for the time.
 
Had you posted a file instead of a picture, would have implemented this for you.

Guess you have to go with a suggestion from one of your cross posts.
 
Thanks but using MOD(A2,1) just returns 0/01/1900.
Click to expand...

Yes, that's right. MOD(A2,1) is the time portion only with no date.
You have the format as Date, and that's what it's displaying, the non existing day, zero, which is January 0, 1900.

What is returned if you format as TIME.
 
Ah thanks, formatting it as time is now showing the time in it.

Yea the two macros in another post work fine except for that change of date format on the last entry which isn't an issue.
 
You must log in or register to reply here.
Back
Top