Consulting

Results 1 to 11 of 11

Thread: Delete all rows under a group

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    Delete all rows under a group

    See the attached sheet. In this spreadsheet, i need to find the word "Closed Accounts" in column B, and delete not only the row that has the word "closed accounts" in column B but also delete all the rows that have text below it. Any ideas on how to do this?
    Attached Files Attached Files

  2. #2
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    269
    Location
    This might work for you...

    Sub deleteAll()
    Dim rng As Range
    Dim rngDelete As Range


    Set rng = Sheet1.Range("B:B")
    Set rngDelete = rng.Find(What:="Closed Accounts")

    Sheet1.Range("B" & rngDelete.Row & ":B" & Sheet1.Range("B65536").End(xlUp).Row).EntireRow.delete

    End Sub

  3. #3
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    There is an error that says object variable or With block variable not set

  4. #4
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by Klartigue
    There is an error that says object variable or With block variable not set
    Presuming there are never going to be any data rows below the row containing the cell with the value, "Closed Accounts," that you want to retain, the following might work for you (of course you may need to change your sheet name as applicable):

    [vba]
    Sub testing()

    Dim rng As Range, fRow, lRow As Integer

    With Sheets("Sheet1")

    lRow = .UsedRange.Rows.Count
    fRow = .Range("B:B").Find(what:="Closed Accounts").Row

    Set rng = .Range("B" & fRow, .Range("B" & lRow))

    rng.EntireRow.Delete

    End With

    End Sub

    [/vba]

  5. #5
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    Thats works great, thank you!

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by Klartigue
    Thats works great, thank you!
    Note that I edited the code in my previous post. It accomplishes the same thing but gets rid of a couple of lines of unnecessary code. Anyway, glad it worked for you.

  7. #7
    VBAX Regular
    Joined
    Feb 2012
    Posts
    31
    Location
    You may find this code useful. If you have more than just one word to look for and delete the entire row, you highlight the column in your case 'B' then run this code. It asks you to put what words to look for in the input box then deletes the entire row.

    Not quite what you are looking for in this case but may come in useful in the future.

    Sub DeleteRowInInputBox ()
     
    Dim s As String, iCol As Long, LR As Long, i As Long
    Dim R As Range, F As Boolean, j As Long
    iCol = ActiveCell.Column
    s = "£"
    Do While s <> ""
        s = LCase(InputBox("Enter word to look for"))
        If s = "" Then Exit Sub
        LR = Cells(Rows.Count, iCol).End(xlUp).Row
        For i = 1 To LR
            If LCase(Cells(i, iCol).Value) Like "*" & s & "*" Then
                If R Is Nothing Then
                    Set R = Cells(i, iCol)
                    j = j + 1
                Else
                    Set R = Union(R, Cells(i, iCol))
                    j = j + 1
                End If
                F = True
            ElseIf Cells(i, iCol).Value <> "" Then
                F = False
            Else
                If F Then
                    If R Is Nothing Then
                        Set R = Cells(i, iCol)
                        j = j + 1
                    Else
                        Set R = Union(R, Cells(i, iCol))
                        j = j + 1
                    End If
                End If
            End If
        Next i
        If Not R Is Nothing Then
            R.EntireRow.Delete
            MsgBox j & " rows deleted", vbInformation
            Set R = Nothing
        End If
        F = False
    Loop
    End Sub
    ------------------------------------------------

    Thanks For All Your Help

    Windows 7

    Excel 2010

    Any codes I provide please try on a copy of your workbook first as these cannot be undone!

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Wouldn't this suffice ?

    [vba]Sub snb()
    Columns(2).Find("Closed Accounts", , xlValues, xlWhole).CurrentRegion.EntireRow.Delete
    End Sub[/vba]

    or

    [vba]Sub snb()
    Columns(2).Find("Closed Accounts", , xlValues, xlWhole).resize(cells(rows.count,2).end(xlup).row).EntireRow.Delete
    End Sub[/vba]

  9. #9
    VBAX Regular
    Joined
    Feb 2012
    Posts
    31
    Location
    Quote Originally Posted by snb
    Wouldn't this suffice ?
    Yes it would. The code I provided is if there are more than one criteria that is needed in one sheet. Rather than going into the code and changing it which would be hard for a beginner, they would just enter the words one after another in the input box.
    ------------------------------------------------

    Thanks For All Your Help

    Windows 7

    Excel 2010

    Any codes I provide please try on a copy of your workbook first as these cannot be undone!

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  10. #10
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by snb
    Wouldn't this suffice ?

    [vba]Sub snb()
    Columns(2).Find("Closed Accounts", , xlValues, xlWhole).CurrentRegion.EntireRow.Delete
    End Sub[/vba]
    or

    [vba]Sub snb()
    Columns(2).Find("Closed Accounts", , xlValues, xlWhole).resize(cells(rows.count,2).end(xlup).row).EntireRow.Delete
    End Sub[/vba]
    I like the brevity. Your suggestion is my first exposure to the CurrentRegion method.

  11. #11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •