Clear Text and Delete

Posted on 2011-05-03
Last Modified: 2012-08-14
I have the attached sub which clears texts and deletes the rows.

Can i amend this so it says <>"DCCHQ0001"

So it will clear the text if its not equal to that text.

Public Sub Sample()
    Set ws = Sheets("Raw Data")
    ClearText ws, 34, "DCCHQ0001"
    ClearText ws, 34, "DCFEE0001"
    ClearText ws, 34, "DCCRT0001"
    ClearText ws, 34, "CAPEL0022"
    ClearText ws, 39, "Nominee Dividend*"
       Application.CutCopyMode = False
End Sub

Public Sub ClearText(osht As Worksheet, Colm As Long, srchText As String)
    ExitLoop = False
    Set oRange = osht.Columns(Colm)

    SearchString = srchText
    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        strbCell = aCell.Address
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=Range(strbCell))
            If Not aCell Is Nothing Then
                ExitLoop = True
            End If

    End If

Open in new window

Question by:Seamus2626
    LVL 33

    Assisted Solution

    Clear the text or delete the entire row if it's not eqqual to DCCHQ0001?

    Author Comment

    Dlete entire row if not equal to "DCCHQ0001"

    LVL 31

    Expert Comment

    by:Rob Henson
    Why Clear the Text and Delete the Row?

    You can just delete the row and the text will go with it.

    Are all the entries of DCCHQ0001 in the same column? If so apply an autofilter to the data and select the dropdown for that column. Choose Equal To and key or select DCCHQ0001 in the right hand box. This will show all rows that have the required text in that column. Select Rows and Delete.

    Rob H
    LVL 31

    Accepted Solution

    Sorry - slight typo; in the Dropdown choose "Not Equal to"

    Rob H

    Author Closing Comment

    All good, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now