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

Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 42

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

  1. #21
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,305
    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
    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.
    Complicates it by adding a helper column.
    Also, what formula would you use - presumably a UDF?

  2. #22
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,305
    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
    I'd love to say this is working, I can't see for the life of me why it isn't
    In what way is it not working?
    Are the blank cells really blank?

  3. #23
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,938
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

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

    footoo

    Why would it be a UDF?

    Also, how does adding a temporary, helper column complicate things?
    If posting code please use code tags.

  4. #24
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,305
    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
    footoo

    Why would it be a UDF?

    Also, how does adding a temporary, helper column complicate things?
    What formula would you suggest?

    Would the helper column not have to remain on the sheet while the filter is in place?

  5. #25
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,938
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by footoo View Post
    What formula would you suggest?
    Not 100% sure but if I recall correctly the criteria, and based on some of the posted code so far, it would be a simple logic formula to check if column U had a date and wasn't empty.

    =AND(ISNUMBER(U2), U2<>"") *


    Quote Originally Posted by footoo
    Would the helper column not have to remain on the sheet while the filter is in place?
    Well yes, but as soon as the data was filtered and whatever required data was extracted/deleted then the 'helper' column could be removed.

    PS If we were to go through the data row by row why not dump it into an array, loop through that array checking the criteria and output the required result to another array.

    * I realise that's a pretty simplistic formula and using ISNUMBER isn't really a sufficient check to see if a value is a date but it could be expanded.
    If posting code please use code tags.

  6. #26
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,305
    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
    Not 100% sure but if I recall correctly the criteria, and based on some of the posted code so far, it would be a simple logic formula to check if column U had a date and wasn't empty.

    =AND(ISNUMBER(U2), U2<>"") *



    Well yes, but as soon as the data was filtered and whatever required data was extracted/deleted then the 'helper' column could be removed.

    PS If we were to go through the data row by row why not dump it into an array, loop through that array checking the criteria and output the required result to another array.

    * I realise that's a pretty simplistic formula and using ISNUMBER isn't really a sufficient check to see if a value is a date but it could be expanded.
    * I realise that's a pretty simplistic formula and using ISNUMBER isn't really a sufficient check to see if a value is a date but it could be expanded
    A UDF would be the simplest way. But then, instead, it would be better to avoid the helper column.
    I would be interested to see what expanded worksheet formula you would suggest.

    Well yes, but as soon as the data was filtered and whatever required data was extracted/deleted then the 'helper' column could be removed.
    Makes it all a bit messy, doesn't it? (particularly if the filtered data is to be amended manually)

    PS If we were to go through the data row by row why not dump it into an array, loop through that array checking the criteria and output the required result to another array.
    Agreed. But the desire to avoid worksheet object loops quite often approaches OCD behaviour even when the run-time saving is immaterial.
    Last edited by footoo; Dec 8th, 2017 at 01:54 AM.

  7. #27
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,938
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

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

    Still not sure why you think using a helper column would be messy.

    The column, with formulas, could be added with one line of code then you would have the filter to get the required data followed by code to copy/delete and finally one line of code to delete the helper column.
    If posting code please use code tags.

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

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

    @footoo @Norie @vcoolio

    Thank you all for taking so much interest in this problem.

    My decision was to use no helper column as I think a simple loop in the column would surfice

    I have a stand alone bas file to apply the formatting highlights I want, so additions could be added to it (you know how it is, you have an idea on the way you expect something to work, so that becomes the intent)

    The SQL provides a single output, that apart from highlights for data inconsistencies (bas file) is ready to use. There will be no adjustment in the output (that is a requirement for the checker to update the programme that holds the staff data so it is right at source)
    • 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

  9. #29
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,542
    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
    In what way is it not working?
    Are the blank cells really blank?
    The cell either contains a value or not, blanks are just that (not hidden nulls or formulas)

    I ran the updated code several times and (blanks) where still in the list along with the broken values, but not a single date value was selected, so all good apart from the (blanks)
    • 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

  10. #30
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,305
    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
    Still not sure why you think using a helper column would be messy.

    The column, with formulas, could be added with one line of code then you would have the filter to get the required data followed by code to copy/delete and finally one line of code to delete the helper column.
    You are assuming that there is no manual manipulation of the data after the filter and before the helper column can be deleted.
    I would guess that the OP wants the filter so that manual amendments can be made to the filtered data.

    I'm not sure why you consider a helper column is not messy.

    What is your suggested worksheet formula to be put in the helper column?

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