Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Delete upon finding

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    How do I do this in VBA? I want it look down column A and if there is an cell with "COMPANY NA" in it, I need it to delete the entire row

    How do I do this?

    Janie

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    Here's some sample code for you to play with. It may not be the most efficient way of doing what you ask but it does work.
    Sub test()

    Dim LastRow As Long

    LastRow = Range("A65536").End(xlUp).Row

    Range("A1").Select

    Do While ActiveCell.Row <= LastRow

    If ActiveCell.Value = "COMPANY NA" Then

    Rows(ActiveCell.Row & ":" & ActiveCell.Row).Delete Shift:=xlUp
    LastRow = LastRow - 1
    End If
    ActiveCell.Offset(1, 0).Select
    Loop

    End Sub


    Regards
    AJ

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Columns("A:A").Find(What:="COMPANY NA").EntireRow.Delete
    It's never too late to learn something new.

    Ricky

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will delete all the occurences of "Company NA"

    Sub Macro1()
    On Error Resume Next
    Err.Clear
    Do While Err.Description <> "Object variable or With block variable not set"
    Columns("A:A").Find(What:="COMPANY NA").EntireRow.Delete
    Loop
    End Sub


    _________________
    It's never too late to learn something new.

    Ricky

    [ This Message was edited by: Ricky Morris on 2002-05-02 08:49 ]

Some videos you may like

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
  •