+ Reply to Thread
Results 1 to 2 of 2

VBA Advanced Filter - Keep Blank Rows and put them at the top

Hybrid View

  1. #1
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    VBA Advanced Filter - Keep Blank Rows and put them at the top

    I have the code below, which works to filter exactly how I need it to except that I would like to be able to include any blank rows at the top of filtered that are within the Range "Table" so I am able to create a filter and then add more items that would be included in that same filter. Those new items would be blank rows of data at first but they would be filled in right after creating a new row/record. Is there any way to modify this so blank rows would just stay at the top and still be visible whenever a filter is applied? Something like ActiveSheet.ShowBlankRows....

    Sub Insert_Row()
    '
    ' Insert_Row
    '
    
    '
        Application.ScreenUpdating = False
    
        ActiveSheet.Unprotect
        
    If ActiveSheet.AutoFilterMode Then
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
    ElseIf ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
        Rows("5:7").Select
        Selection.EntireRow.Hidden = False
        Rows("8:10").Select
        Selection.EntireRow.Hidden = False
        Range("A6:AH6").Select
        Selection.Copy
        Range("A10:AH10").Select
        Selection.Insert Shift:=xlDown
        Rows("6:6").Select
        Selection.EntireRow.Hidden = True
        Rows("9:9").Select
        Selection.EntireRow.Hidden = True
        
        Range("Table").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("B4:AH5"), Unique:=False
    
        
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
            AllowUsingPivotTables:=True
        
        Range("B10").Select
        
        Application.ScreenUpdating = True
        
    End Sub


    Thanks for your help!
    -------------
    Tony

  2. #2
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    Re: VBA Advanced Filter - Keep Blank Rows and put them at the top

    I was able to find a way around this problem with a solution I'm happy with.

    I was not able to find anything at all on the Internet to cause empty rows within the table range to be placed at the top of the list when there is a filter applied and judging by that and the lack of responses to this post, it's not possible. The answer came to me this morning just as I was waking up... Seems I do my best thinking when I'm half in and half out of sleep. I was making this far more difficult than it needed to be. Since I needed the new record (row) to be visible when inserted while a filter was applied using the entry in the filter cell B5, I used a copy and paste function in my code to copy the filter cell and paste the value into the new record B10 cell so it becomes within the parameters of the filter. New record stays visible and at the top of the other filtered records. Works like a charm.

    Sub Insert_Row()
    '
    ' Insert_Row
    '
    
    '
        Application.ScreenUpdating = False
    
        ActiveSheet.Unprotect
        
    If ActiveSheet.AutoFilterMode Then
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
    ElseIf ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
        
        Range("A6:AH6").Select
        Selection.Copy
        Range("A10:AH10").Select
        Selection.Insert Shift:=xlDown
        
        Range("B5").Copy
        Range("B10").PasteSpecial xlPasteValues
        
        Range("Table").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("B4:AH5"), Unique:=False
       
        Rows("4:4").Select
        Selection.EntireRow.Hidden = True
        
        Rows("6:6").Select
        Selection.EntireRow.Hidden = True
        
        Rows("9:9").Select
        Selection.EntireRow.Hidden = True
        
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
            AllowUsingPivotTables:=True
       
        Range("B10").Select
        
        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)

Similar Threads

  1. Delete Rows hidden by either Auto Filter and Advanced Filter
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:29 AM
  2. Advanced Filter to return Rows w/blank column
    By ChemistB in forum Excel General
    Replies: 3
    Last Post: 04-01-2009, 01:14 PM
  3. Advanced Filter Macro With Field Not Blank
    By mr63249 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2008, 11:10 AM
  4. Advanced Filter - Blank Rows
    By karl_009 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-30-2008, 06:35 AM
  5. Replies: 2
    Last Post: 03-21-2006, 02:45 PM

Tags for this Thread

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