View Full Version : Vba Code to filter a Column automatically
chichaspowa
10-29-2010, 01:09 PM
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
10-29-2010, 05:41 PM
Try This
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
chichaspowa
10-29-2010, 08:59 PM
pasted it on the vba of the worksheet and no changes, still the same, did i paste it wrong?
chichaspowa
10-29-2010, 09:08 PM
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
10-30-2010, 07:19 AM
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.
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
chichaspowa
10-30-2010, 11:34 AM
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
10-30-2010, 02:22 PM
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.
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
frank_m
10-30-2010, 02:40 PM
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
10-30-2010, 03:43 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.