Is there a way (VBA) Selection in Array - Page 2

Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 42

Thread: Is there a way (VBA) Selection in Array

  1. #11
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,417
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

     
    Quote Originally Posted by mole999 View Post
    some of the values might look like 13/13/1959 so obviously not a valid date, other have bits of text mixed in, but always formatted as **/**/****, and testing the column for less than a number didn't work.
    Not sure if this was a response to my suggestion, but do you have any numbers in the range that aren't dates?

    Or are you saying that some "valid" dates got picked up by the filter? If so, I'd suggest those dates are actually text and not true Excel serial dates, in which case we could probably take efficient steps to convert them before applying the filter.
    [code]your code[/code]

  2. #12
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,289
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Quote Originally Posted by Norie View Post
    Why not, instead of looping, use code to add a formula based on the values in the field/column you actually want to filter?

    The formula could determine which rows to keep/delete and you could then filter on this 'helper' column.
    Why make it so complicated?

  3. #13
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,540
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    @FormR

    I have a complex SQL statement to use on a string to generate a date from an encoded driving licence

    ,CASE WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 9 THEN '-X-' WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 8 THEN '-X-' WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 10 THEN '-X-' WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 12 THEN '-X-' WHEN Convert(varchar(10),PRP.DOFB,3) <> SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),9,2)+'/'+case when isnumeric(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1)) = 1 then replace(REPLACE(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1),'5','0') ,'6','1') else SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1) end +''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),8,1)+'/'+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),6,1)+''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),11,1) then SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),9,2)+'/'+case when isnumeric(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1)) = 1 then replace(REPLACE(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1),'5','0') ,'6','1') else SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1) end +''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),8,1)+'/'+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),6,1)+''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),11,1) else '' end as [DL Date]

    it generates some values like
    "(D/03/4V", "//", "12/95/E1", "15/19/95", "32/-2/09", "32/80/76", "61/40/07", "9E/09/NJ", "C1/No/-L", "GV/E9/N5", "-X-"

    Post 7 above does mostly what I need apart from its including BLANKS in the selection (which I need to exclude) for cleaning and reporting purposes
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  4. #14
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,417
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Quote Originally Posted by mole999 View Post
    I have a complex SQL statement
    You sure do!
    [code]your code[/code]

  5. #15
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,540
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    yep that's one of about 20 that are dealt with on the FLY fixing variants that get added
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  6. #16
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,289
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Quote Originally Posted by mole999 View Post
    Post 7 above does mostly what I need apart from its including BLANKS in the selection (which I need to exclude) for cleaning and reporting purposes
    Code:
    Dim notDate() As String, cel As Range, i%
    i = 0
    ReDim notDate(0)
    For Each cel In [U2:U6699]
    If Not IsDate(cel) And Not IsEmpty(cel) Then
        notDate(i) = cel
        i = i + 1
        ReDim Preserve notDate(i)
    End If
    Next
    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=1, Criteria1:=notDate, Operator:=xlFilterValues

  7. #17
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,540
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Quote Originally Posted by footoo View Post
    Code:
    Dim notDate() As String, cel As Range, i%
    i = 0
    ReDim notDate(0)
    For Each cel In [U2:U6699]
    If Not IsDate(cel) And Not IsEmpty(cel) Then
        notDate(i) = cel
        i = i + 1
        ReDim Preserve notDate(i)
    End If
    Next
    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=1, Criteria1:=notDate, Operator:=xlFilterValues
    I'd love to say this is working, I can't see for the life of me why it isn't
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  8. #18
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,914
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Footloo

    How would it be complicated to add a formula in a column and use that to filter?

    It's basically what you are doing without the loop.
    If posting code please use code tags.

  9. #19
    Board Regular
    Join Date
    Oct 2015
    Location
    Italy
    Posts
    402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

    Hi mole999,

    maybe I am making a big mistake but what about this:

    Code:
    ActiveSheet.Range("$A$1:$BE$6699").AutoFilter Field:=21, Criteria1:="=*/*", Operator:=xlOr, Criteria2:="=*-*"
    at the end date has no "/"... It is only the way excel shows a number representing a date. Every data including "/" is something else.

    Hope this helps

    .___ ................___
    | _ \ ..............| _ \
    | _ < ..............|. _/
    |___/.___..___..___.|_|
    .....|___||___||___|

  10. #20
    Board Regular
    Join Date
    Jun 2014
    Posts
    433
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way (VBA) Sekection in Array

      
    Hi Mole999,

    I have a complex SQL statement to use
    Between you Poms and us Aussies, I think that the saying is "Bloody Hell!"

    Does the idea of a list to filter on not suit? I also think that Norrie's idea of a helper column is a good option but...........

    Cheerio,
    vcoolio.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com