[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Clear Text and Delete

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.

Thanks
Seamus
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
        aCell.EntireRow.Delete
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=Range(strbCell))
            If Not aCell Is Nothing Then
                aCell.EntireRow.Delete
            Else
                ExitLoop = True
            End If
        Loop

    End If

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
2 Solutions
 
jppintoCommented:
Clear the text or delete the entire row if it's not eqqual to DCCHQ0001?
0
 
Seamus2626Author Commented:
Dlete entire row if not equal to "DCCHQ0001"

Thanks
Seamus
0
 
Rob HensonIT & Database AssistantCommented:
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.

Cheers
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Sorry - slight typo; in the Dropdown choose "Not Equal to"

Cheers
Rob H
0
 
Seamus2626Author Commented:
All good, thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now