Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Delete rows hidden by autofilter.

  1. #1
    Join Date
    11th April 2003
    Location
    South Scotland
    Posts
    120

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,785
    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

  3. #3
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,785

  5. #5
    Join Date
    11th April 2003
    Location
    South Scotland
    Posts
    120
    Thanks for that Dave, it works perfectly!!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Delete Rows After AutoFilter
    By ahmd20 in forum EXCEL HELP
    Replies: 3
    Last Post: January 5th, 2008, 05:28
  2. Delete All Rows Hidden By Autofilter
    By mike22 in forum EXCEL HELP
    Replies: 7
    Last Post: October 4th, 2007, 13:27
  3. Delete Hidden Rows
    By georgebrough in forum EXCEL HELP
    Replies: 2
    Last Post: August 18th, 2007, 02:04
  4. Vba To Delete The Hidden Rows In A Worksheet
    By kasakouda in forum EXCEL HELP
    Replies: 1
    Last Post: April 11th, 2007, 00:40
  5. copy skipping hidden rows or hidden columns value
    By ilyaskazi in forum EXCEL HELP
    Replies: 4
    Last Post: February 25th, 2006, 18:13

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
  •  
porno