simple delete

Posted on 2014-07-11
Last Modified: 2014-07-11
I have spent too mucjh time looking for this so have given up I know it's on EE somewhere but......

All I want to do id find the word market [but it could be something Market something in column A and for each time I find this I want to delet the row.

I thought this might work but No

Range("A:A").Find("*Market8", , xlValues, xlWhole, , , False).Select

please help it's Friday and I want to get off home asap.

Question by:Jagwarman
    LVL 19

    Accepted Solution

    Try this:

    Sub KillIt()
        Dim SearchRange
        Dim i As Long
        Dim position As Long
        Dim targetString As String
        targetString = "Market"
        SearchRange = Range("A1", Range("A65536").End(xlUp))
        For i = Range("A65536").End(xlUp).Row To 1 Step -1
            position = InStr(LCase(SearchRange(i, 1)), LCase(targetString))
            If position > 0 Then
                Range("A" & i).EntireRow.Delete shift:=xlShiftUp
            End If
        Next i
    End Sub

    Open in new window


    Author Comment

    excellent. Now I can go home :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Title # Comments Views Activity
    Time Clock in Excel issues 5 26
    How to calculate the  QUARTILE ? 5 25
    excell formula 4 17
    Surpressing messages VBA 9 29
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    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…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now