Announcement

Collapse
No announcement yet.

Delete rows hidden by autofilter.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete rows hidden by autofilter.

    Hi again folks,

    A quicky here: Can anyone help me with some VBA that will delete all the rows in my worksheet that are hidden, upon the application of custom autofilter?

    Thanks in advance,
    Damian

  • #2
    Hi Damian

    Try this code;
    Code:
    Sub DeleteHiddenRows()
    Dim lRows As Long
    Application.Calculation = xlCalculationManual
        For lRows = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
            If Cells(lRows, 1).EntireRow.Hidden = True Then Cells(lRows, 1).EntireRow.Delete
        Next lRows
    Application.Calculation = xlCalculationAutomatic
    End Sub

    Comment


    • #3
      I haven't used autofilter much but my good buddy Trev' gave me this a while back and it will delete hidden rows.

      Code:
      Public LastDataRow As Long
      Public Sub DeleteHiddenRows()
      On Error GoTo THEEND
      
      Dim lngDelete As Long
      
      lngDelete = 0
      LastRowWithData
      
      Application.ScreenUpdating = False
      For i = 1 To LastDataRow
          Application.StatusBar = ". . . . . . . scanning Row:" & Str(i)
          If Rows(i).Hidden = True Then
              Rows(i).Delete
              lngDelete = lngDelete + 1
          End If
      Next
      
      Application.ScreenUpdating = True
      Application.StatusBar = ""
      Cells(1, 1).Select
      
      THEEND:
      End Sub
      Tim.

      Comment


      • #4
        Hi Tim

        When deleting rows you must work backwards from the last row to the first.

        Comment


        • #5
          Thanks for that Dave, it works perfectly!!

          Comment

          Working...
          X