Please explain this AutoFilter procedure

[RyanH]

I got this code from someone about a year ago and I am tring you understand
how autofilter works, becuase I would like to start using it in the future.

1.) When you use AutoFilter on a Range the filter range does not include
the first row of the worksheet, right? I guess AutoFilter assumes a header
row, right? Because of this I insert a blank row at the top of the worksheet
so all my data is included in the filter. Is there a way around this?

2.) I get lost on this line:

Set rngFilter = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

Why do I need to use Offset and Resize? I thought when the AutoFilter is
used it only displays the data you requested to see. So I should beable to
just use Set rngFilter = .SpecialCells(xlCellTypeVisible), why not?


Private Sub OrganizeNewData()

SubName = "OrganizeNewData"

Dim i As Long
Dim rngProductCodes As Range
Dim myArr As Variant
Dim rngFilter As Range

' insert header row so all data will be filtered,
' autofilter does not include the first row
Sheets("New Data").Rows(1).Insert Shift:=xlDown

'Fill in the values that you want to delete
myArr = Array("H - Hold", "CR - Retained - Faces", "GH - Hold -
Graphics", "Z - Do Not Print")

For i = LBound(myArr) To UBound(myArr)

'Sheet with the data
With Sheets("New Data")

' remove the AutoFilter
.AutoFilterMode = False

' apply the filter
.Range("M1:M" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=myArr(i)

Set rngFilter = Nothing
With .AutoFilter.Range

' resize the filtered range
On Error Resume Next
Set rngFilter = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

' delete filtered range if something is found
If Not rngFilter Is Nothing Then
rngFilter.EntireRow.Delete
End If
End With

' remove the AutoFilter
.AutoFilterMode = False
End With
Next i
End Sub

 

[Nigel]

I suspect it is offsetting the filter headings, thereby only selecting the
filtered data.



--

Regards,
Nigel
(e-mail address removed)

 

[Sheeloo]

1. First row is supposed to have Headers. Technically filtered range does
include the first row but data is from row 2 down...

2. Resize is changing the range to EXCLUDE the header row before deleting
the filtered rows...

Hope the explanation is clear.

 

[RyanH]

Thanks for the reply. Doesn't .Rows.Count count all the rows in the
worksheet? If so, what is the point in this case to count all the rows then
minus 1? Also, what is the point in resizing to another column?

The ultimate goal here is too delete all the rows the filter returns or shows.

 

[Sheeloo]

Not necessarily. It returns the count of rows in the range it is called on...
In this case it is used with .AutoFilter.Range - the filtered range...
Also there is a - 1 in the resize...

If you delete the header row (do not resize) the next AutoFilter will
fail... rather it will take the first row as header and delete that also even
if it does match the criteria...

 

[RyanH]

Ok, I understand that part, but what about .Resize(.Rows.Count - 1, 1). Why
is there a 1 in the Column Parameter?

I am wanting to delete the entire row. But for some reason when I set the 1
to 0 it does not delete anything. Any ideas?

 

[Dave Peterson]

Say you have data in A1:X999 and you filter those 999 by column J.

The "With .AutoFilter.Range" means that the code only looks at that range--not
the entire worksheet.

' resize the filtered range
On Error Resume Next
Set rngFilter = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

The .offset(1,0) means to come down 1 row and move over 0 columns.
So instead of looking at A1:X999, you're looking at A2:X1000.

But the resize says to only look at a single column (Column A in this case) and
one fewer rows. So the range you're looking at is now: A2:A999.

You're avoiding the header row and changing the range to a single column.

Then the .specialcells(xlcelltypevisible) limits that range to just the visible
cells in column A of the filtered range (excluding the headers).

So when you do this:

rngFilter.EntireRow.Delete

It's deleting the entire row where you can see column A's cell.

=======
And you can't .resize() a range to 0 columns. You can .offset() by 0 columns,
but not resize to 0 columns.

======
It would be a little better to write the statement this way:

Set rngFilter = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)


If the entire column were filtered (all 64k or 1M rows), then doing the
..offset() first would result in an error. You'd be offsetting past the last row
in the worksheet! (But the other explanations still stands.)