PDA

View Full Version : Solved: select a cell and filter data automatically



CCkfm2000
09-20-2005, 04:35 AM
Help...

I've got a spreadsheet with over 1000 location.
When I click on any cell in sheet (Loc) I then need the 2nd sheet to filter automatically out all but the cell I've selected.
I've attached an example spreadsheet.
Any help will be much appreciated
Many thanks

Killian
09-20-2005, 04:52 AM
In the code for Sheet("Loc") you need to use the Selection_Change event to trigger thr filter on Sheet("Inven")Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Inven").Range("A1").AutoFilter Field:=1, Criteria1:=Target.Value
End Sub

CCkfm2000
09-20-2005, 05:12 AM
will try it out

thanks

CCkfm2000
09-20-2005, 05:24 AM
sorry to be a pain,
it works ok but not with two cells merged together eg, a1 + b1

malik641
09-20-2005, 05:34 AM
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target is nothing then exit sub

Sheets("Inven").Range("A1").Autofilter Field:=1, Criteria1:=Target(1,1).value
End Sub



Try that.

CCkfm2000
09-20-2005, 06:24 AM
what a hero..... it works :cloud9:

thanks :clap:

malik641
09-20-2005, 07:31 AM
Hey anytime CCkfm2000, glad to help :thumb

CCkfm2000
10-10-2005, 04:55 AM
help another problem with the above...

what i need to do now is that if a cell has eg. y1 I then need the 2nd sheet to filter automatically the following y1a, y1b,y1c,y1d,y1e,y1f,y1g. out.

Bob Phillips
10-10-2005, 06:20 AM
Change to

Criteria1:=Target.Value & "*"

CCkfm2000
10-10-2005, 06:38 AM
thanks for the quick reply,
works ok but when I select a cell eg. Y1 I get Y10 as well, i just need the 1 criteria.
thanks again.

malik641
10-10-2005, 03:20 PM
This is more difficult than I thought it would be. I have some code and I think I'm heading in the right direction, but I need some help with the code...it's really buggin' me out http://vbaexpress.com/forum/images/smilies/119.gif

I'm trying to assign the AutoFilter criteria to only use letters and not numbers with your Target value. Here's what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target Is Nothing Then Exit Sub
Dim Chars(65 To 90, 97 To 122) As Variant
Dim i As Variant
Dim j As Variant

For i = 65 To 90
For j = 97 To 122
Chars(i, j) = Asc(i) & Asc(j)
Exit For
Next
j = j + 1
Next

Sheets("Inven").Range("A1").AutoFilter Field:=1, Criteria1:=Target(1, 1).Value & "*" & (Chars)
End Sub I just can't get this to work right (mostly because I'm not familiar with using VBA Arrays...which BTW, does anybody have any good references to using VBA arrays?)

CCkfm2000
10-14-2005, 12:41 AM
can i use advanced filter with Selection_Change event?

CCkfm2000
10-14-2005, 01:41 AM
i've managed to get this far.
now i need to now use advanced filter within this vba code


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target Is Nothing Then Exit Sub

If Target(1, 1).Value <> "" Then

End If
MsgBox ("You Selected :- " & Target(1, 1).Value)

Sheets("SideInven").Range("n2") = Target(1, 1).Value & "A"
Sheets("SideInven").Range("n3") = Target(1, 1).Value & "B"
Sheets("SideInven").Range("n4") = Target(1, 1).Value & "C"
Sheets("SideInven").Range("n5") = Target(1, 1).Value & "D"
Sheets("SideInven").Range("n6") = Target(1, 1).Value & "E"
Sheets("SideInven").Range("n7") = Target(1, 1).Value & "F"
Sheets("SideInven").Range("n8") = Target(1, 1).Value & "G"
Sheets("SideInven").Range("n9") = Target(1, 1).Value & "H"

' ***** can't get the next line to work **** :think:

Sheets("SideInven").Range("a1:k17273").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("N1:X9"), CopyToRange:=Columns("AA:AK"), Unique:=False

End Sub


thanks

CCkfm2000
10-14-2005, 06:46 AM
:clap: thanks to all for all the help on this...

i've managed to modify the orignal code supplied by Killian.
a bit long but it works...


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target Is Nothing Then Exit Sub

If Target(1, 1).Value <> "" Then

End If
Sheets("SideInven").Range("n2") = Target(1, 1).Value & "A"
Sheets("SideInven").Range("n3") = Target(1, 1).Value & "B"
Sheets("SideInven").Range("n4") = Target(1, 1).Value & "C"
Sheets("SideInven").Range("n5") = Target(1, 1).Value & "D"
Sheets("SideInven").Range("n6") = Target(1, 1).Value & "E"
Sheets("SideInven").Range("n7") = Target(1, 1).Value & "F"
Sheets("SideInven").Range("n8") = Target(1, 1).Value & "G"
Sheets("SideInven").Range("n9") = Target(1, 1).Value & "H"

Sheets("SideInven").Range("a1:k17273").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("SideInven").Range("N1:X9"), CopyToRange:=Sheets("SideInven").Columns("AA:AK"), Unique:=True
Sheets("SideInven").Select
End Sub



thanks again :cloud9:

malik641
10-14-2005, 11:19 AM
Just so you know, I've placed your VB code in VB Tags :thumb

Here's a little something that makes your code a little more efficient:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target Is Nothing Then Exit Sub

If Target(1, 1).Value <> vbNullString Then
With Sheets("SideInven")
.Range("n2") = Target(1, 1).Value & "A"
.Range("n3") = Target(1, 1).Value & "B"
.Range("n4") = Target(1, 1).Value & "C"
.Range("n5") = Target(1, 1).Value & "D"
.Range("n6") = Target(1, 1).Value & "E"
.Range("n7") = Target(1, 1).Value & "F"
.Range("n8") = Target(1, 1).Value & "G"
.Range("n9") = Target(1, 1).Value & "H"

.Range("a1:k17273").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
.Range("N1:X9"), CopyToRange:=Sheets("SideInven").Columns("AA:AK"), Unique:=True
.Select
End With
End If
End Sub

CCkfm2000
10-18-2005, 11:22 PM
thanks...