ExcelBanter - Autofilter for fixed columns

I like to be specific with the range I'm filtering.

In most cases, I can pick out a column that always has data in it -- and that
can define the last row to include in the filtered range.

And I can usually pick out a row that can be used to determine last column to
use.

In this case, I used column A and row 1 to find the extent of the range to
filter.

And I wanted to start the filter in A1.

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim LastRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
.AutoFilterMode = False 'remove any existing filter
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If LastCol < .Range("ac1").Column Then
MsgBox "not enough data!"
Exit Sub
End If

Set myRng = .Range("A1", .Cells(LastRow, LastCol))

myRng.AutoFilter field:=.Range("j1").Column, Criteria1:="John, C"
myRng.AutoFilter field:=.Range("ac1").Column, Criteria1:="<"
myRng.AutoFilter field:=.Range("r1").Column, Criteria1:="<"

End With

End Sub

choo wrote:

Hi,
I have a big table from column A to BP, though the number of column is not
fixed. Sometimes it has additonal one or 2 extra columns append at the back,
so it could reach until column BR.

Regardless of how many columns I have in this table, I want to have a macro
that could turn on autofilter, filter column J for a value e.g. "John, C",
and then filter column AC and R for non-blank cells.

I tried the following, but it doesn't work.

.Columns("J:J").AutoFilter field:=1, Criteria1:="John, C"
.Columns("AC:AC").AutoFilter field:=1, Criteria1:="<"
.Columns("R:R").AutoFilter field:=1, Criteria1:="<"

Can anyone help?

choo

--

Dave Peterson