Vba Code to filter a Column automatically

[chichaspowa]

Hello all,

I have a little problem regarding the autofilters on a normal excel table.

I have the last column of my table set to filter all 0 values, but since i am inputting the information via another worksheet, everytime i input info the table does not update automatically and i have to manually go into the filter and choose select all and then filter the 0 value again to see the new results.

I would like to know if there is some code i can put on the worksheet so that everytime it has data it can show it and continue filtering the 0 values.

The sheet name is ("concentração") and its the 18th column of my table.

Thank you in advance.

Vic

[frank_m]

Try This
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long

If ActiveSheet.FilterMode = True Then
'' Exit if data is filtered - Range does not need to be reset.
Exit Sub
Else
'' Run code
Set wks = ActiveSheet
With wks
LastRow = .[R65536].End(xlUp).Row '' R is Column 18

'' In this example Row one is the Header & Column 18 get's the Filter
Set rng = .Range(.Cells(1, 18), .Cells(LastRow, 18))

If rng Is Nothing Then
MsgBox "There was a problem setting the Filter Range"
Exit Sub
Else
.AutoFilterMode = False
rng.AutoFilter
.EnableAutoFilter = True
End If
End With
End If

End Sub[/vba]

[chichaspowa]

pasted it on the vba of the worksheet and no changes, still the same, did i paste it wrong?

[chichaspowa]

when i click on a worksheet cell it gives men error "autofilter method of range class failed"...

but apart from that i probably should have told u im updating the table via vlookup.

thx frank

[frank_m]

Hi chichaspowa,

I get that error if the sheet is empty, or if Cell R1 is empty.

I really do not know anything about updating tables with vlookup.

Do you have a heading in cell R1 ? If no, perhaps you can post a sample workbook.

Perhaps the safe guards that I added below may help.
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long

If Application.CutCopyMode = xlCut _
Or Application.CutCopyMode = xlCopy _
Or WorksheetFunction.CountA(Cells) = 0 _
Or Selection.Cells.Count > 1 _
Or Range("R1").Value = "" Then Exit Sub ''("R1") being empty may cause an error, or filter on wrong row.

If ActiveSheet.FilterMode = True Then
'' Exit if data is filtered - Range does not need to be reset.
Exit Sub
Else
'' Run code
Set wks = ActiveSheet
With wks
LastRow = .[R65536].End(xlUp).Row '' R is Column 18

'' In this example Row one is the Header & Column 18 get's the Filter
Set rng = .Range(.Cells(1, 18), .Cells(LastRow, 18))

If rng Is Nothing Then
MsgBox "There was a problem setting the Filter Range"
Exit Sub
Else
.AutoFilterMode = False
rng.AutoFilter
.EnableAutoFilter = True
End If
End With
End If

End Sub[/vba]

[chichaspowa]

ok i have sent the sample...

just so u understand what i mean i will try to explain better..

on menu, register new cheque... i fill in the details and register.. the details are copied to a table and then on sheet "concentração" the details are updated via vlookup the client.... i would like to filter the last column to only show me values above 0. But whenever i register a new cheque and go to concentração i have to manually go into the filters and uncheck the boxes and re check the boxes. before the table is updated. i hope i explained myself ok... thx frank

[frank_m]

HI Hi chichaspowa,

I don't know where you want the code, so I added a couple of command buttons to demonstrate the code that I came up with.
- Hopefully it will work ok as part of your other code.

I attached a sample workbook that contains the code and command buttons shown below.
[vba]Private Sub CommandButton2_Click()
''// SET FILTER RANGE and Filter Column 18 for values greater than zero
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long

'If Application.CutCopyMode = xlCut _
'Or Application.CutCopyMode = xlCopy _
'Or Selection.Cells.Count > 1 _
'Or Range("R14").Value = "" Then Exit Sub ''("R1") being empty may cause an error, or filter on wrong row.
'// If using in the selection change event, replace command below with the code above
If Range("R14").Value = "" Then
MsgBox "You cannot work with the Filter if Range(R14) is Empty"
Exit Sub ''("R1") being empty may cause an error, or filter on wrong row.
End If

If ActiveSheet.FilterMode = True Then
'' Exit if data is filtered - Range does not need to be reset.
Exit Sub
Else
'' Run code
Set wks = ActiveSheet
With wks
LastRow = .[R65536].End(xlUp).Row '' R is Column 18

'' In this example Row 14 is the Header & Column 18 get's the Filter
Set rng = .Range(.Cells(14, 2), .Cells(LastRow, 18)) 'Range Col 2 to Col 18

If rng Is Nothing Then
'' MsgBox "There was a problem setting the Filter Range"
Exit Sub
Else
'' Release all filters. To release only Column 18 filter Uncomment Field:=17
ActiveSheet.ListObjects("Table5").Range.AutoFilter ' Field:=17
DoEvents
rng.AutoFilter
End If
End With
End If

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=17, Criteria1:= _
">0", Operator:=xlAnd

End Sub


Private Sub CommandButton3_Click()
''// RELEASE FILTERS and reset range
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long

If Range("R14").Value = "" Then
MsgBox "You cannot work with the Filter if Range(R14) is Empty"
Exit Sub ''("R1") being empty may cause an error, or filter on wrong row.
End If

Set wks = ActiveSheet
With wks
LastRow = .[R65536].End(xlUp).Row '' R is Column 18

'' In this example Row 14 is the Header & Column 18 get's the Filter
Set rng = .Range(.Cells(14, 2), .Cells(LastRow, 18)) 'Range Col 2 to Col 18

If rng Is Nothing Then
' MsgBox "There was a problem setting the Filter Range"
Exit Sub
Else
'' Release all filters. To release only Column 18 filter Uncomment Field:=17
ActiveSheet.ListObjects("Table5").Range.AutoFilter ' Field:=17
DoEvents
rng.AutoFilter
End If
End With

End Sub[/vba]

[frank_m]

I reread you last post. If I understand correctly you would like to have a way to have all the check box's that are greater than zero checked and the zero check box un-checked by default or by code for the column 18 Filter. - That I have no idea if it's possible. My guess is that it's not easy, but I'm stabbing in the dark, as I'm not an expert. - When I read your original post I thought you were describing that when you added a new row of data to the Table it was not showing up when filtered. -> If that is not your problem then you probably do not need to use the code I posted for setting the filter range.

Also in your first post it I understood what you wrote to mean that you want your filter results to show only the zero values.
- But by you last post it seems that you want to filter results for values above zero.

[chichaspowa]

yes im sorry i explained myself badly, i want the greater than 0 values to show and the rest to be hidden.

I inserted your code and changed the r column to s since it was filtering the wrong column and gonna try making a refresh button out of it. Its not exactly how i wanted it but it sure is better than what it was.

Thanks Frank i apreciate it. U might not be an expert yet but u seem pretty close. Thanks once more for ur great help.

Vic