Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: deleting rows based on one cell's contents

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Oregon
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I delete, say, 3 or 4 rows based on the contents of what's in one cell of the first row in that series? For example:

    Cell A1 says 4/11/02. I want to delete rows 1-5, and also the same number of rows beneath any instance where "4/11/02" appears in column A.

    Can you help? Thanks!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    NY
    Posts
    1,056
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub Deleter()
    del = InputBox("Text to delete + 4 rows?", , ActiveCell.Value)
    On Error Resume Next
    1:
    Set rw = Columns(1).Find(del, lookat:=xlWhole)
    If Not rw Is Nothing Then
    rw.Resize(5).Delete
    GoTo 1
    End If
    End Sub

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

    Default

    We'll see if that works (it's not for me).

    Thanks much!

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

    Default

    Wow, that didn't work at all. Either I'm retarded (distinct possibility), it doesn't work in Excel 97 (don't know why that would happen), or there's some other issue.

    VBS is exceedingly foreign to me. The following script was posted in another thread I found that only deleted the row holding the offending text. They look very different. If my idiocy doesn't annoy too much, could someone explain the difference?

    Sub del_rows()
    While ActiveCell.Value = "?"
    If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
    ActiveCell.Offset(1, 0).EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Wend
    End Sub


    Oh, and yes, I am replacing the question marks and such with the correct information. I am almost, but not quite, that dumb.

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

    Default

    The macro posted by B. Umlas does what you asked for.

    Presumably, you are looking to delete a variable number of rows rather than a fixed number such as 5 per the macro.

    Try this :-

    Sub Deleter()
    del = InputBox("Text to delete", , ActiveCell.Value)
    On Error Resume Next
    1:
    Set rw = Columns(1).Find(del, after:=[A65536], lookat:=xlWhole)
    If Not rw Is Nothing Then
    rw.EntireRow.Delete
    GoTo 1
    End If
    End Sub

    If this does not do what you want, perhaps you should post some more detailed info as to what you need (some before and after data).

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
  •