Deleting Rows with text
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Deleting Rows with text

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have various data in a column, and I want to include deleting them in a formula macro.

    For example, I want the macro to run so that whenever it sees ANY ROW which contains the Text XX, YY, or ZZ etc it erases it.


    So the macro would basically look for any of those three values and delete the entire row they are in. It would keep deleting ALL the rows until there are no more occurrences of the text.



  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something like:


    Option Explicit
    Option Base 1

    Sub Delete_Txt()
    Dim x As Single
    Dim Kount As Double
    Dim sSearch()

    '// Define your string to search for here
    sSearch() = Array("XX", "YY", "ZZ")
    Kount = 0
    Application.ScreenUpdating = False

    For x = 1 To UBound(sSearch())
    On Error Resume Next
    Do
    Cells.Find(What:=sSearch(x), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    Selection.EntireRow.Delete
    If Err Then Exit Do
    Kount = Kount + 1
    Loop
    On Error GoTo 0
    Next
    Application.ScreenUpdating = True

    MsgBox "Completed deleting " & Kount & " rows"

    End Sub


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi,

    Brute force method here. Try,

    ---begin VBA---
    Sub Delete_Rows()
    Dim lastrow As Long, lastcol As Integer
    Dim x As Long, y As Integer, Counter As Long

    Application.ScreenUpdating = False
    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column

    For x = lastrow To 1 Step -1
    Counter = 0
    Counter = Counter + _
    WorksheetFunction.CountIf(Range(Cells(x, 1), Cells(x, lastcol)), "XX") + _
    WorksheetFunction.CountIf(Range(Cells(x, 1), Cells(x, lastcol)), "YY") + _
    WorksheetFunction.CountIf(Range(Cells(x, 1), Cells(x, lastcol)), "ZZ")
    If Counter <> 0 Then Rows(x).delete
    Next x

    End With

    End Sub
    ---end VBA---

    HTH,
    Jay

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
  •  

 

 
DMCA.com