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.)



RyanH wrote:

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
--
Cheers,
Ryan

--

Dave Peterson