Re: Sorting Data based on Dep Time and Reg Code using VB code
Hello Jindon, I tested it on actual data and it works perfect, many thanks, really appreciated.
Sorting Data based on Dep Time and Reg Code using VBA
-
-
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
Quote from jindon;606657So change you Macro4 (sorting macro) to
Hello Jindon,
If you remember the above code you gave me , this actually sorts the data by flight number disregarding the hidden value in column A , I need to do many sorting ligics ( e.g I want to sort the data by "Trip No. 2" and "Dep Time" ( the earliest first ), this hidden value in column is messing up my data.
Is there any code that I can insert in any module so that before I run any sorting , 1st the hidden values in column A are removed then the sorting done without considering any blank or $ or NIL vlaues in the blank cells?
Thanks.[TABLE="width: 81"]
[tr]
[td][/td]
[/tr]
[/TABLE] -
Re: Sorting Data based on Dep Time and Reg Code using VB code
Replace Sheets("Sheet1") with ActiveSheet.
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
But, the above code only sorts the data by Flight No. am I right?
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
The follwoing part is cleaning your data
CodeWith .UsedRange.Columns(1) .Value = Evaluate("if(" & .Address & "<>"""",trim(clean(" _ & .Address & ")),"""")") End With
And the rest is the sort part.
Once it is cleaned, no need to run the code, so sort the data as you like. -
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
Thanks, I will try it and let you know.
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
Hello Again jindon, Thank you for your help,
I have been busy testing the code using different scenarios, I thought the code will actually remove those blank cells , but it does not. it would very much help if the code can actually delete/remove all the blank cells .
Is it possible?
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
try
Code
Display MoreSub Macro4() With Sheets("Sheet1") With .UsedRange.Columns(1) .Value = Evaluate("if(" & .Address & "<>"""",trim(clean(" _ & .Address & ")),"""")") End With .Rows(.Range("a" & Rows.Count).End(xlUp)(2).Row & ":" & Rows.Count).Clear With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 10) .Sort .Range("a1"), 1 End With End With End Sub -
Re: Sorting Data based on Dep Time and Reg Code using VB code
Thanks Jindon, I will try it and let you kow.
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
Hello Jindon,
When I run the code , the MACRO freezez , I have to force stop it, any Idea why?
Thanks.
-
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
Can you upload the file?
I can not replicate the problem here.
-
Re: Sorting Data based on Dep Time and Reg Code using VB code
Thanks, I will soon.
-
Re: Sorting Data based on Dep Time and Reg Code using VBA
Hello Jindon , I tested the Code on a separate sheet and it is working , I will continue to see why it is not doing so in the real data.
Something came up in my company just today; they now decided to use a flight# and Reg Code as a trigger to distribute the data:- I know it is a challenge , but thank you for your help.
Here is a simple logic how it works:
a) Any Flight number range 20 – 2999 and range 9000 - 9099
1) with the first two Reg code $XP to be copied to Desk 1
2) with the first two Reg code $XQ to be copied to Desk 2
3) with the first two Reg code $XR to be copied to Desk 3
4) with the first two Reg code $XS to be copied to Desk 4
5) with the first two Reg code $XT to be copied to Desk 5
6) with the first two Reg code $XV to be copied to Desk 6
7) with the first two Reg code $XW to be copied to Desk 7b) Any other Flight numbers with any Reg code other than the above , to be copied to Desk 8
I already created 8 sheets and named them Desk1 to 8.
DataBase[TABLE="width: 0"]
[tr]
[td]Flight#
[/td]
[td]Dep Time
[/td]
[td]Arr Time
[/td]
[td]Reg Code
[/td]
[td]Pax Count
[/td]
[td]Dep Airport
[/td]
[td]Arr Airport
[/td]
[td]Class
[/td]
[td]Trip No.1
[/td]
[td]Trip No.2
[/td]
[/tr]
[tr]
[td]MMM570
[/td]
[td]900
[/td]
[td]955
[/td]
[td]$XPSL
[/td]
[td][/td]
[td]EGLL
[/td]
[td]EDDF
[/td]
[td]XX80
[/td]
[td]43000333
[/td]
[td]43000333
[/td]
[/tr]
[tr]
[td]MMM9010
[/td]
[td]1600
[/td]
[td]1655
[/td]
[td]$XPSL
[/td]
[td][/td]
[td]EDDF
[/td]
[td]EGLL
[/td]
[td]XX80
[/td]
[td]43000333
[/td]
[td]49000333
[/td]
[/tr]
[tr]
[td]MMM6074
[/td]
[td]40
[/td]
[td]420
[/td]
[td]$XQD
[/td]
[td]480
[/td]
[td]LIRF
[/td]
[td]EGLL
[/td]
[td]23B
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM7444
[/td]
[td]45
[/td]
[td]435
[/td]
[td]$XWI
[/td]
[td]446
[/td]
[td]LEMD
[/td]
[td]LIMC
[/td]
[td]23B
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM3773
[/td]
[td]100
[/td]
[td]350
[/td]
[td]$XRX
[/td]
[td][/td]
[td]EDDF
[/td]
[td]EGLL
[/td]
[td]22A
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM733
[/td]
[td]120
[/td]
[td]735
[/td]
[td]$OXH
[/td]
[td]285
[/td]
[td]EDDK
[/td]
[td]EDDF
[/td]
[td]B70
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM4860
[/td]
[td]511
[/td]
[td]540
[/td]
[td]$93S
[/td]
[td][/td]
[td]LIRR
[/td]
[td]LIRR
[/td]
[td]123
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM2900
[/td]
[td]600
[/td]
[td]945
[/td]
[td]$XST
[/td]
[td]433
[/td]
[td]LIMC
[/td]
[td]LIMJ
[/td]
[td]22A
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM450
[/td]
[td]615
[/td]
[td]800
[/td]
[td]$XST
[/td]
[td]424
[/td]
[td]LIMJ
[/td]
[td]LIMC
[/td]
[td]22A
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM2844
[/td]
[td]650
[/td]
[td]745
[/td]
[td]$XTX
[/td]
[td][/td]
[td]EDDF
[/td]
[td]EGLL
[/td]
[td]B70
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM4766
[/td]
[td]720
[/td]
[td]840
[/td]
[td]$OVD
[/td]
[td]480
[/td]
[td]EGLL
[/td]
[td]LIRF
[/td]
[td]23B
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM4860
[/td]
[td]730
[/td]
[td]855
[/td]
[td]$XVV
[/td]
[td]465
[/td]
[td]LIRR
[/td]
[td]LIRF
[/td]
[td]123
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td]MMM4537
[/td]
[td]735
[/td]
[td]945
[/td]
[td]$XQI
[/td]
[td]480
[/td]
[td]LIMC
[/td]
[td]LEMD
[/td]
[td]23B
[/td]
[td]NIL
[/td]
[td]NIL
[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]$
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]NIL
[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]$
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]$
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]$
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]$
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]$
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/TABLE] -
Re: Sorting Data based on Dep Time and Reg Code using VBA
Can you upload a falie with your desired result?
And I suggest you to open a new thread as it seem to be different from your original question.
-
Re: Sorting Data based on Dep Time and Reg Code using VBA
Thanks Jindon,
I will open a new thread and upload a sample.
-
-
Re: Sorting Data based on Dep Time and Reg Code using VBA
I just posted a new thread called " Distributing data to multiple sheets based on 2 criteria "
Thank you again for your help, really appreciate it.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!