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


"Sheeloo" wrote:

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" 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