+ Reply to Thread
Results 1 to 8 of 8

InStr Date Regex Match to Delete Row

  1. #1
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    InStr Date Regex Match to Delete Row

    Hello, I'm looking to delete an entire row from data, if a date in a cell before the underscore is the same as the date after it. Rest of the characters can be set to anything with regex. All cells in column 'D' follow this format:
    XX.XXXXXXXXddmmyyyy_ddmmyy_X123456

    I've tried using InStr as well as some sort of regex to solve this but couldn't really get it to work the way I wanted to, perhaps due to the date formatting or the fact that the same cell is being compared with itself, and so I'll greatly appreciate your help with this.

    Thanks!

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: InStr Date Regex Match to Delete Row

    UDF
    Use in cell like
    =ConsecutiveDates(A1)
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: InStr Date Regex Match to Delete Row

    Is regexp necessary here?
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: InStr Date Regex Match to Delete Row

    @jindon
    Thank you but for some reason it shows 'FALSE' for the cells where the dates do appear twice. Perhaps something to do with regex?
    It can go like: anythingDDMMYYYY_DDMMYYanything and the bits DDMMYY must match.

    @Jacc
    Thank you. This does work fine for individual cells.
    Would you be so kind please and explain how this could be changed so that a loop could go through all of column 'D', as well as delete the entire row instead of showing a msgbox? Not sure how I could add the 'LastRow' functionality here for this loop because I'm assuming this would probably be the best choice?

    Many thanks!

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: InStr Date Regex Match to Delete Row

    Quote Originally Posted by Frenzyy View Post
    @jindon
    It can go like: anythingDDMMYYYY_DDMMYYanything and the bits DDMMYY must match.
    I thought it would be ddmmyyyy vs ddmmyyyy...
    change pattern to
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: InStr Date Regex Match to Delete Row

    Thanks for your help!

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: InStr Date Regex Match to Delete Row

    Quote Originally Posted by Frenzyy View Post
    @Jacc
    Thank you. This does work fine for individual cells.
    Would you be so kind please and explain how this could be changed so that a loop could go through all of column 'D', as well as delete the entire row instead of showing a msgbox? Not sure how I could add the 'LastRow' functionality here for this loop because I'm assuming this would probably be the best choice?

    Many thanks!
    I can. If you would be so kind and please post a workbook with sample data as instructed in the yellow box at the top of the thread.

  8. #8
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: InStr Date Regex Match to Delete Row

    I actually managed to do it already but I really appreciate your willingness to do so. Have a great day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Instr function withing Regex
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-03-2019, 04:21 AM
  2. [SOLVED] Regex exact match function
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 05-03-2019, 02:40 AM
  3. [SOLVED] Instr with RegEx - Runtime error 5017 (RegEx Code provided)
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2018, 05:02 PM
  4. Is there a way to delete the rows which column A value match a certain regex?
    By woshichuanqilz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2017, 07:16 AM
  5. [SOLVED] If Instr - find last row of data above and delete everything in between
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2013, 03:54 PM
  6. [SOLVED] REGEX to delete all non hex characters
    By creeves95833 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2012, 05:43 PM
  7. Using INSTR to Match Value in ComboBox
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2011, 04:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1