Autofilter not hiding first blank cell/row

Jockster

Board Regular
Joined
Jan 16, 2009
Messages
88
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,362
Office Version
365
Platform
Windows
try amending these lines

Code:
Set GetRange = Range("A[COLOR=#ff0000]6[/COLOR]:H" & lastrow) 

[COLOR=#ff0000].Offset(1)[/COLOR].SpecialCells(xlCellTypeVisible).Copy
 

Jockster

Board Regular
Joined
Jan 16, 2009
Messages
88
try amending these lines

Code:
Set GetRange = Range("A[COLOR=#ff0000]6[/COLOR]:H" & lastrow) 

[COLOR=#ff0000].Offset(1)[/COLOR].SpecialCells(xlCellTypeVisible).Copy
Thanks so much Yongle, this worked like a charm!

Cheers.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,362
Office Version
365
Platform
Windows
thanks for the feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,665
Messages
5,488,180
Members
407,631
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top