Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: VBA Delete Entire Row if Doesn't Contains Certain Text

  1. #11
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,393
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Delete Entire Row if Doesn't Contains Certain Text

    Quote Originally Posted by maxbmx View Post
    you last one working great but I want to switch column A to D

    and I'm getting error for:

    Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
    If your part numbers are in column D instead of column A, the only change required is
    Code:
    a = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value
    Do not change the "A2" near the bottom of the code.

    I have not been able to reproduce the error you report. If you still get the error, please provide the full error message and what the value of nc is when you click Debug and hover over nc in the yellow line of code.

    Quote Originally Posted by maxbmx View Post
    And can we keep row 1 pls?
    My code does not touch row 1 anyway.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  2. #12
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Entire Row if Doesn't Contains Certain Text

    Hi Peter,
    Working great but only getting that small issue about row 1 that got delete and row 2 come the row 1

    thank a lot for your time on my story

  3. #13
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,393
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Delete Entire Row if Doesn't Contains Certain Text

    Quote Originally Posted by maxbmx View Post
    getting that small issue about row 1 that got delete and row 2 come the row 1
    Please copy and post the full code that you are now using.

    As I stated before, my code does not do anything with row 1. My code interacts with the worksheet twice only.

    First time is to read all the column D data starting from row 2 only into memory.

    Second time is to write some temporary data back to the sheet and get rid of the unwanted rows but that whole section only deals with the sheet from row 2 downwards.

    Code:
    Sub DelUnwantedParts_v2()
      Dim a As Variant, b As Variant
      Dim nc As Long, i As Long, k As Long
      Dim bFound As Boolean
      
      nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
      a = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value '<- That is, from row 2 downwards
      ReDim b(1 To UBound(a), 1 To 1)
      For i = 1 To UBound(a)
        bFound = False
        Select Case True
          Case InStr(1, "IN|CH|EL|EV|EX|F0|F3|F7|GF|IK|KM|SH", Left(a(i, 1), 2), 0) > 0: bFound = True
          Case InStr(1, "D|T", Left(a(i, 1), 1), 0) > 0: bFound = True
        End Select
        If Not bFound Then
          k = k + 1
          b(i, 1) = 1
        End If
      Next i
      If k > 0 Then
        Application.ScreenUpdating = False
        With Range("A2").Resize(UBound(a), nc) '<- That is, from row 2 downwards
          .Columns(nc).Value = b
          .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
          .Resize(k).EntireRow.Delete
        End With
        Application.ScreenUpdating = True
      End If
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #14
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Entire Row if Doesn't Contains Certain Text

    Hi Peter,
    I understand but unfortunately row 1 got delete and row 2 come the row 1

    Code:
    Sub DelUnwantedParts_v2()
      Dim a As Variant, b As Variant
      Dim nc As Long, i As Long, k As Long
      Dim bFound As Boolean
      
      nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
      a = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value
      ReDim b(1 To UBound(a), 1 To 1)
      For i = 1 To UBound(a)
        bFound = False
        Select Case True
          Case InStr(1, "IN|CH|EL|EV|EX|F0|F3|F7|GF|IK|KM|SH|D3|TI|TK", Left(a(i, 1), 2), 0) > 0: bFound = True
     End Select
        If Not bFound Then
          k = k + 1
          b(i, 1) = 1
        End If
      Next i
      If k > 0 Then
        Application.ScreenUpdating = False
        With Range("A2").Resize(UBound(a), nc)
          .Columns(nc).Value = b
          .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
          .Resize(k).EntireRow.Delete
        End With
        Application.ScreenUpdating = True
      End If
    End Sub
    Last edited by Peter_SSs; Oct 7th, 2019 at 06:53 PM. Reason: Added Code tags

  5. #15
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Entire Row if Doesn't Contains Certain Text

    Peter,
    Sorry all good, working great now

    bug on my side

    Thanks

  6. #16
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,393
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Delete Entire Row if Doesn't Contains Certain Text

    Quote Originally Posted by maxbmx View Post
    Peter,
    Sorry all good, working great now

    bug on my side

    Thanks
    You're welcome. Glad you got it sorted.

    BTW, when posting code, please use Code Tags to preserve the indentation in the code, making it easier for others to read/debug, copy with indentations. I have added them to post 14 for you and my signature block below explains how to do it.
    Last edited by Peter_SSs; Oct 7th, 2019 at 06:56 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

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
  •