Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,716
    Hi Damian

    Try this code;
    VB:
    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.

    VB:
    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,716

  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, 04:28
  2. Delete All Rows Hidden By Autofilter
    By mike22 in forum EXCEL HELP
    Replies: 7
    Last Post: October 4th, 2007, 12:27
  3. Delete Hidden Rows
    By georgebrough in forum EXCEL HELP
    Replies: 2
    Last Post: August 18th, 2007, 01:04
  4. Vba To Delete The Hidden Rows In A Worksheet
    By kasakouda in forum EXCEL HELP
    Replies: 1
    Last Post: April 10th, 2007, 23:40
  5. copy skipping hidden rows or hidden columns value
    By ilyaskazi in forum EXCEL HELP
    Replies: 4
    Last Post: February 25th, 2006, 17: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