Deleting an entire row in VBA Macro
Results 1 to 4 of 4

Thread: Deleting an entire row in VBA Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting an entire row in VBA Macro

    I've created a filter for an account #, if that account # appears in my data file, I want to delete the entire row. This has worked before, but now I'm getting a debug error

    Code:
     lRow = ActiveSheet.Range("A500").End(xlUp).RowIf AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
    lRow = ActiveSheet.Range("A500").End(xlUp).Row
        
        With ActiveSheet
            .AutoFilterMode = False
            With Range("a8:W" & lRow)
                .AutoFilter
                .AutoFilter Field:=6, Criteria1:="469777"
               
            End With
        End With
    Code:
     If Range("A8:A" & lRow).SpecialCells(xlCellTypeVisible).Count > 1 Then
    Code:
            ActiveSheet.Range("a8:W" & lRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If


    Error - Run-time error '1004' - no cells were found

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,519
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Deleting an entire row in VBA Macro

    Try
    Code:
       With ActiveSheet
          If .AutoFilterMode = True And .FilterMode = True Then .ShowAllData
          lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
          .Range("a8:W" & lRow).AutoFilter Field:=6, Criteria1:="469777"
          .AutoFilter.Range.Offset(1).EntireRow.Delete
          .ShowAllData
       End With
    This replaces all the code that you have shown
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Deleting an entire row in VBA Macro

    It's perfect, thank you

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,519
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Deleting an entire row in VBA Macro

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •