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
|
Similar Threads
