Filter, Sort, Macro... what to do?

zingle

Beginner

Points

30

Posts

4
Oct 16th 2015
- #1
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 like this
[Blocked Image: http://i.imgur.com/Cs9YZAF.jpg]

I want to have say only only the rows which have morning data between 10:00 and 12:00.
[Blocked Image: http://i.imgur.com/8j0d3xe.jpg]

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.

Oct 16th 2015

#2

Re: Filter, Sort, Macro... what to do?

See if this works,

Code

Sub test1()
    Dim x, n As Long
    With Cells(1).CurrentRegion
        With .Offset(1)
            x = Filter(Evaluate("transpose(if((hour(" & .Columns(1).Address & ")>=10)*(hour(" & _
            .Columns(1).Address & ")<=12),row(2:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
        End With
        n = UBound(x)
        If n < 0 Then
            MsgBox "No data"
        Else
            x = Split("1," & Join(x, ","), ",")
            x = Application.Index(.Value, Application.Transpose(x), Evaluate("column(" & .Address & ")"))
            .Offset(, .Columns.Count + 2).Resize(UBound(x, 1), UBound(x, 2)).Value = x
        End If
    End With
End Sub

Display More

Oct 16th 2015

#3

Re: Filter, Sort, Macro... what to do?

Here's another possibility..

Code

Private Sub CommandButton1_Click()
    With [A1].CurrentRegion
        .Offset(1, .Columns.Count).Resize(.Rows.Count - 1, 1).Formula = "=IF(AND(HOUR(A2)>=10,HOUR(A2)<12), 1,0)"
        .Resize(, .Columns.Count + 1).AutoFilter .Columns.Count + 1, 1
        .Copy Sheets("Sheet2").[A1]
        .AutoFilter
        .Offset(, .Columns.Count).Resize(.Rows.Count, 1).ClearContents
    End With
End Sub

zingle

Beginner

Points

30

Posts

4
Oct 16th 2015
- #4
Re: Filter, Sort, Macro... what to do?

Thanks that works great and if I change to time in the macro to get another range it works fine except for a slight niggle.

With the data the macro copies the date cell for the final entry is formatted as general and in 12 hours time e.g.

[Blocked Image: http://www.ozgrid.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAR8AAABRCAIAAABkEDWyAAAH%20ElEQVR4nO1dXZqDIAzsuTyQ5/E0Pcm%20eRj2oVsNYRK01iXJl3krFR0Mw19h%20vhJJBL34PHz81MSEeE9sgH4p7rCwntkA/BPdYWF98gG4J/qCgvvkQ3AP9UVFt4jG4B/qissvEc2AH%20qrnWZHhTzs5N/Xab%20RYlBoLXzOZ8KrAlU6iIF2Ng/58djWtZSismqCNT1ZvtXnv0jgsEiJTYwdW2hXJfJhcB2/hXj54wqpcGqqKqrvAT2pkw6tvlZWEdHWxBfDWRgSOoicX3Oj2lZZhIvQxGs1QUk9S6G0arYUxcNA2UtdMdcjHrHl7gZorr22LzGW3sITUWwmbNwNpytsap4XF0EpBxVkXjuurlM/DskdZHpSh1fYxFkqxrvmVc9d4HqslGQE%20qqVzwkdTGkukZCXNXA9dNcBOGa4bpMsG0wWBUPz7uEcjRFyrmWIcjzLprM1GUogsKK/M5SVdf4gqjqElqJ51z3XWg4n7CA0%20oyFsGN/7pMdXcr9F3GqqL%20e1fTjf2lLnOzkIgWanJgOBjn1VVMRVComcK41l5VzL0akeE9sgH4p7rCwntkA/BPdYWF98gG4J/qCgvvkQ3A//GZYUAikegi%2066w8B7ZAPxTXWHhPbIB%20Ke6wsJ7ZAPwT3WFhffIBuAfU11ob/9/5DUF75ENwD%20kulJcpYSonaMpXIIbdYHTc/ysNEG9qe7jvPh0q5C3eQo8stMrlHROUL1AOoVrMLKAau34Qb%20h/KuQko28408gy3Chrte7fNa7ntlOab7h%20P35Ql6yMZvYOsC88Cn6KQiF2FPe340uwDxLMagumeoGOnLA6qI5D9xwIFyo64XWUUc6zdNuB7%20St76Blnc9oy6YBSc2LYB8vKL%20xnRkDxQdqUset5s4dFLBq7q0wzxAIFfyvtM3exTxEBH42Dv/8G111XXPdGSRTFhaoy6lV7c4X46hLj5X0uvrybxVupxXya4MWg6p61gukG44srAIXB9MXefbqcGIoS7yCb/kK3kbS8qjfRe/h7Su8S11NV2cXXW1VAsqVTsy1GZr1rTlV13S/EeY63yel4ftxLxLfH7vi0/UBauXzcgKSgANEJp3AX3ZlFZxrC5u9KGK68O81FekqHnBU6hhrBz%20r8y7IM9SLKpLpAo7JWFFng8mbEqrOFEX/0Gk%20aWDvmoWoQt5299huPUckVL7FJomNNUNMcyW/QigPYjyNKgukSpuf8TfuzYtymW3ABfqOoojS%20B35DUL75ENwD%20MulJcHN4jG4B/GHUlOLxHNgD/VFdYeI9sAP7pq5FI3IXsu8LCe2QD8E91hYX3yAbgn%20oKC%20%20RDcA/1RUW3iMbgH9MdeXR/xKido6mcAlR1ZXiKiVE7RxN4RLcqOvLvhqKfcVYXw2lUOIF4oMMRhaXb98uWL0Mwv8vHzpuif%20xy0L76EJdN/lq4IOuBnw1cKHkC8QH2YvsfnJgHyHQkopnq9dlekwT%20FfveZ731wDiNRIu1PXC93w1tMShvhrCzWsVtxc4UteOreSK9QJT1/xsxyQ8jd99KLyq64qvhpY41FdDuvmmLuEC8UGGI8s64a1xqD406qriA5Kq22ffdQJf89WQEwf7aqg3P3yBcdeabYoFp12PaZo0ddUDyvdo4lBjOgAx1HXOV0NMHO2rod780AVy228OQovT6bv2CzZRNeqyYwTgVV2XfDWExPG%20GtrNj11Q3890ZPuNA1RXM9uq1WVHWsWxui75asDENi/0bLjVVwPfHM9R9kZcfJC5yK7LVP2MAGwa6HuC6uKL89UiryFpFSfq%20rqvBko04asBb15XuvYC%20UH2IosWYEgae09YXUyYdIgI4zUOLtR1FEeWwO/IaxbeIxuAfxh1pbg4vEc2AP8w6kpweI9sAP6prrDwHtkA/NNXI5G4C9l3hYX3yAbgn%20oKC%20%20RDcA/1RUW3iMbgH%20qKyy8RzYA/6vqykP2ZhGgdo6mcAnX1ZXisosAtXM0hUsA6mrNHjSvh66DhZL5Hx0slNJJiagEdAdtb08bJK%209yv2tNN%20yjav1pfK%20VYO1E7%20Avq9GXdHq7b56%20AaCqUswe6D7RZudrvTLD90vsCPCtxws6AMlhvKfyWNuzX1RxRfI47dB38o8s/KsS9dVAsGeuj721eBb4ckOeTV8QwFHhgdPI5X%208dlz7hf1Db7sYKGXjifiyt/L2jHA6J7OKu%203wr/kZ5qkNAZ76tpR%20RZUNQafTyNHkfE4wbu6lN4H59XO0v63g4VeumPqkjqpuiFtaszfpwMni7e3gpuPo64SbxhW11lfjVdBpT7KtbqQF8KRair7QEivY0%20X8yrZxcHbKdpnMuKFGUheLRHP1krxsKvEDovq%20tBXY7vm2KDKBs6PDKszbJ3Bk9haC0%20408FCL10/PLA36zcS5FO375Lf3TZS7LtKUFhU1xtCO6j3XauQK4S6uEC6DhZn3C9udrDQr%20hkE9YgZCV/Mu%20qVz6rhpq/OslVgsGyuo4MBdC8C%20rLr7pW6IUgLSS0w58j7hfrcruDhcQQJ5L7QG7bF1U5dAMMtUSAOn7bbHHel7pwXdqg%20Gq0q9O00D7UBc0e0JoBENcF94t7HSx0hph2vfqHuCEhdwwwpMQtr7hiQl9ZXS19qUusS/iliL937e9AqHU28OFejVNL4F/MmzgFe%20o6hwD8P1BXissHAtTO0RQuIffIR4b3yAbgn%20oKC%20%20RDcA/fTUSibvwC21wn1U%20TXnUAAAAAElFTkSuQmCC]

The bottom entry is always different to all the others. No big deal since it is easy enough to change that one cell but it seems odd that it is doing it.

Thanks again.

Oct 16th 2015

#5

Re: Filter, Sort, Macro... what to do?

If you are talking about my code then change to

Code

Sub test1()
    Dim x, n As Long, myFormat As String
    With Cells(1).CurrentRegion
        With .Offset(1)
            myFormat = .Cells(1).NumberFormat
            x = Filter(Evaluate("transpose(if((hour(" & .Columns(1).Address & ")>=10)*(hour(" & _
            .Columns(1).Address & ")<=12),row(2:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
        End With
        n = UBound(x)
        If n < 0 Then
            MsgBox "No data"
        Else
            x = Split("1," & Join(x, ","), ",")
            x = Application.Index(.Value, Application.Transpose(x), Evaluate("column(" & .Address & ")"))
            With .Offset(, .Columns.Count + 2).Resize(UBound(x, 1), UBound(x, 2))
                .CurrentRegion.ClearContents
                .Value = x
                .Columns(1).NumberFormat = myFormat
            End With
        End If
    End With
End Sub

Display More

zingle

Beginner

Points

30

Posts

4
Oct 16th 2015
- #6
Re: Filter, Sort, Macro... what to do?

I don't know if its the code or what, that one does the same thing.
Date Pre Site
10/10/2015 11:12 27 K2
10/11/2015 10:05 39 M2
13/10/2015 11:27:00 AM 39 K1

Is what it returns, like I said no big deal, it just seems odd that the last entry is always different.
jindon

Excel Samurai

Reactions Received

3

Points

47,063

Posts

9,398
Oct 16th 2015
- #7
Re: Filter, Sort, Macro... what to do?

Can you upload a sample workbook?
apo

Professional

Points

7,345

Posts

1,431
Oct 16th 2015
- #8
Re: Filter, Sort, Macro... what to do?

Hi zingle..

Have you tried my code and/or the Workbook i attached?

jindons.. by default is faster than mine (as expected using an array based solution).. but i don't see the issue with the formatting using my autofilter/copy code...

Perhaps you could have a cup of tea whilst waiting for my code to run..
Valuable Resources:

snb's Website:
http://www.snb-vba.eu/index_en.html

Smallmans Website:
http://www.thesmallman.com/
NoSparks

Beginner

Points

290

Posts

58
Oct 16th 2015
- #9
Re: Filter, Sort, Macro... what to do?

Hey zingle zongle, have you checked your cross posts ?
cytop

.

Reactions Received

3

Points

55,213

Posts

10,880
Oct 16th 2015
- #10
Re: Filter, Sort, Macro... what to do?

Or, more to the point...
MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
*
zingle

Beginner

Points

30

Posts

4
Oct 17th 2015
- #11
Re: Filter, Sort, Macro... what to do?

I'll put up a sample after the weekend when I get back in.

Yes Apo I tried your code and it is the same as jindons, it does the job fine except the date in the last entry is always in a different format. As I said it isn't an issue but would be nice to know what it is doing that.

And yeah i've checked the cross posts, most being the MOD to extract the time so have linked to the macros here that do the whole job.

Thanks to you both.
cytop

.

Reactions Received

3

Points

55,213

Posts

10,880
Oct 17th 2015
- #12
Re: Filter, Sort, Macro... what to do?

Please add links to your posts on other boards for this issue.

If you need further explanation what this means then read this.
cytop

.

Reactions Received

3

Points

55,213

Posts

10,880
Oct 17th 2015
- #13
Re: Filter, Sort, Macro... what to do?

Closed to save anyone wasting any more time with this - I'm sure the code here (mentioned but uncredited on another forum) along with the suggestions in one of the 5 other boards this is posted on should be usable.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.