Prev: Spin Button Programming
Next: Save to SFTP not working.
From: choo on 8 Mar 2010 09:49
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
From: Dave Peterson on 8 Mar 2010 11:35
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
 | 
Pages: 1
Prev: Spin Button Programming
Next: Save to SFTP not working.