+ Reply to Thread
Results 1 to 2 of 2

Code Amendment - Delete Empty Rows

Hybrid View

  1. #1
    Registered User
    Join Date
    09-08-2011
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    70

    Smile Code Amendment - Delete Empty Rows

    Hi all,

    I have the code below which should find the last populated row and delete everything below it. Essentially I have 5 formatted pages of cells populated with vlookups - to allow anything from 1 to 350 results to display after importing a file.

    Once I've imported the file, the VBA below should delete all the rows which have not been populated. The only other point is I have an initial page header of 13 rows.

    Sub Filter_and_Delete_Filtered_Rows()
    
        Range("A13:A300").Select
        Selection.AutoFilter
        ActiveSheet.Range("A13:A300").AutoFilter Field:=1, Criteria1:="=0", _
            Operator:=xlOr, Criteria2:="="
        Cells.Select
        Selection.Delete Shift:=xlUp
    End Sub
    It does delete all the blank rows after my data, but it also deletes the first 13 rows?

    Any ideas anyone?

    Many thanks in advance!

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Code Amendment - Delete Empty Rows

    Sub filter()
    Application.ScreenUpdating = False
    On Error Resume Next
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("A13:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
            .AutoFilter Field:=1, Criteria1:="=0", Operator:=xlOr, Criteria2:="="
            .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
             .AutoFilterMode = False
        End With
    End With
    Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1