Results 1 to 4 of 4

Thread: Autofilter not hiding first blank cell/row

  1. #1
    Board Regular
    Join Date
    Jan 2009
    Location
    Isle of Man
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Autofilter not hiding first blank cell/row

    The issue I am having with autofilter is that it isn't hiding the first row of a range when the first cell in the filtered column of that range is empty.
    Code snippet:

    Code:
    If Range("G1") > 0 'Then 'competitors' times have been entered
       lastrow = Range("G" & Rows.Count).End(xlUp).Row 'identify the last used row
    Set GetRange = Range("A7:H" & lastrow) 'range to filter/copy
            With GetRange
                .AutoFilter
                .AutoFilter Field:=6, Criteria1:="<>"
                .SpecialCells(xlCellTypeVisible).Copy
                Range("BB7").PasteSpecial Paste:=xlPasteValues
                .AutoFilter
            End With
    Row 6 contain the headers which should not be copied
    Row 7 is the start of the data
    Column 6 (F) contains the times (in HH:MM) and is the column I am filtering on.
    Cell F6 contains no time therefore it should be hidden by autofilter but it isn't

    I have tried varying the range to "A6:H" & lastrow and whilst this did hide the blank row 6, when 'copy' was applied it started it row 9 and ignored 8 which has a value.

    Any suggestions welcomed!

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,268
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Autofilter not hiding first blank cell/row

    try amending these lines

    Code:
    Set GetRange = Range("A6:H" & lastrow) 
    
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy

  3. #3
    Board Regular
    Join Date
    Jan 2009
    Location
    Isle of Man
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofilter not hiding first blank cell/row

    Quote Originally Posted by Yongle View Post
    try amending these lines

    Code:
    Set GetRange = Range("A6:H" & lastrow) 
    
    .Offset(1).SpecialCells(xlCellTypeVisible).Copy
    Thanks so much Yongle, this worked like a charm!

    Cheers.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,268
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Autofilter not hiding first blank cell/row

    thanks for the feedback

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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