Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 43

Thread: Delete entirerow if cellvalue in column A has "xxxx" as year

  1. #1
    Board Regular
    Join Date
    Apr 2015
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete entirerow if cellvalue in column A has "xxxx" as year

    Please take me out of my misery.
    I think I'm going insane from google search overload.

    I have a workbook with 5000+ entries. It has dates in the format (dd.mm.yy) in Column A.

    All I need to do is delete ALL the rows that are not between 2005 and 2010 (inclusive both years)

    For example:

    (1) 22.02.14
    (2) 01.01.07
    (3) 02.12.01

    So, Rows 1 and 3 would be deleted and 01.01.07 is kept because it is between 2005 and 2010.

    Please help me, I got over 6 workbooks to do this and really can't do this manually.

    Below is the code I sort of made. Doesn't work ofcourse but brilliant code for crashing computers.

    James

    Code:
    Sub DeleteRows()
        
        Dim i As Long
        Dim st As Long
        Dim en As Long
        Dim test As Long
    
        'Set ranges to four digit year code'
        st = 2005
        en = 2010
    
        For i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        
        test = Val(WorksheetFunction.Text(ActiveSheet.Range("A" & i).Value, "yyyy"))
        If test < st Or test > en Then
        ActiveSheet.Range("A" & i).EntireRow.Delete
        End If
        Next i
        
    End Sub

  2. #2
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    this seems to work for me

    Code:
    Sub DeleteRows()
        
        Application.ScreenUpdating = False
        Dim i As Long
        
        For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
               
        If Year(Range("A" & i)) < "2005" Then
        
        ActiveSheet.Range("A" & i).EntireRow.Delete
        
        End If
       
          
        If Year(Range("A" & i)) > "2010" Then
        
        ActiveSheet.Range("A" & i).EntireRow.Delete
        
        End If
            
            
        Next i
      
        Application.ScreenUpdating = True
    End Sub
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

  3. #3
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,242
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    See if this works for you:
    Code:
    Sub DeleteRows()
        
        Dim cll As Range
        Dim Rng2Del As Range
    
        'Set ranges to four digit year code'
        Const st = 2005
        Const en = 2010
    
        Application.ScreenUpdating = False
        For Each cll In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If (Year(cll.Value) < st) Or (Year(cll.Value) > en) Then
                If Rng2Del Is Nothing Then
                    Set Rng2Del = cll
                Else
                    Set Rng2Del = Application.Union(Rng2Del, cll)
                End If
            End If
        Next cll
        Rng2Del.EntireRow.Delete
        Application.ScreenUpdating = True
    End Sub

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    Quote Originally Posted by Tetra201 View Post
    See if this works for you:
    Thanks i understand the or option from your code

    my condensed code is

    Code:
    Sub DeleteRows()
        Application.ScreenUpdating = False
        Dim i As Long
        For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Year(Range("A" & i)) < "2005" Or Year(Range("A" & i)) > "2010" Then
        ActiveSheet.Range("A" & i).EntireRow.Delete
        End If
        Next i
        Application.ScreenUpdating = True
    End Sub
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

  5. #5
    Board Regular
    Join Date
    Apr 2015
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    Quote Originally Posted by Tetra201 View Post
    See if this works for you:
    Code:
    Sub DeleteRows()
        
        Dim cll As Range
        Dim Rng2Del As Range
    
        'Set ranges to four digit year code'
        Const st = 2005
        Const en = 2010
    
        Application.ScreenUpdating = False
        For Each cll In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If (Year(cll.Value) < st) Or (Year(cll.Value) > en) Then
                If Rng2Del Is Nothing Then
                    Set Rng2Del = cll
                Else
                    Set Rng2Del = Application.Union(Rng2Del, cll)
                End If
            End If
        Next cll
        Rng2Del.EntireRow.Delete
        Application.ScreenUpdating = True
    End Sub

    Thank you for your reply.

    There is a debug error on line "If (Year(cll.Value) < st) Or (Year(cll.Value) > en) Then" it says type mismatch?

  6. #6
    Board Regular
    Join Date
    Apr 2015
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    Hello there Akatrouble,

    I have tried your updated code.

    There is a mismatch debug error on line "If Year(Range("A" & i)) < "2005" Or Year(Range("A" & i)) > "2010" Then"

    Thank you
    Last edited by Eyeson15; Feb 24th, 2017 at 09:17 PM.

  7. #7
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    are you sure the dates in the cells are dates and not text ??
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

  8. #8
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,242
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    @AkaTrouble:

    One more thing: your code deletes non-conforming rows one by one; mine deletes them all at once -- much faster.

  9. #9
    Board Regular
    Join Date
    Apr 2015
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    Quote Originally Posted by AkaTrouble View Post
    are you sure the dates in the cells are dates and not text ??

    The dates are in custom format "dd.mm.yy". Definetely not text
    Last edited by Eyeson15; Feb 24th, 2017 at 09:20 PM.

  10. #10
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,242
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete entirerow if cellvalue in column A has "xxxx" as year

    Quote Originally Posted by Eyeson15 View Post
    The dates are in custom format "dd.mm.yy". Definetely not text
    You have text strings, not real Excel dates.

    Try this:
    Code:
    Sub DeleteRows()
        
        Dim cll As Range
        Dim Rng2Del As Range
    
        Const st = 5
        Const en = 10
    
        Application.ScreenUpdating = False
        For Each cll In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If (CByte(Right(cll.Value, 2)) < st) Or (CByte(Right(cll.Value, 2)) > en) Then
                If Rng2Del Is Nothing Then
                    Set Rng2Del = cll
                Else
                    Set Rng2Del = Application.Union(Rng2Del, cll)
                End If
            End If
        Next cll
        Rng2Del.EntireRow.Delete
        Application.ScreenUpdating = True
    End Sub

Some videos you may like

User Tag List

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
  •