Hi Naaitie,
You should have started a new thread with this question!
Here is a macro doing the job!
Make the first row a header row otherwise Autofilter doesn't work correctly:
A1: | header |
A2: | abcd.. |
A3: | efgh.. |
A4: | abcd.. |
A5: | rstq.. |
A6: | abcd.. |
Sub abcdfilter()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=abcd*", Operator:=xlAnd
Dim wholerng As Range, filteredrng As Range
Dim cell As Range
Set wholerng = ActiveSheet.AutoFilter.Range.Columns(1)
Set wholerng = wholerng.Offset(1, 0).Resize(wholerng.Rows.Count - 1)
On Error Resume Next
Set filteredrng = wholerng.SpecialCells(xlVisible)
On Error GoTo 0
If Not filteredrng Is Nothing Then
filteredcount = 1
For Each cell In filteredrng
filteredcount = filteredcount + 1
Range("C" & filteredcount) = cell.Address(False, False)
Next cell
End If
Selection.AutoFilter
End Sub
Regards,
Stefi
€˛naaitie€¯ ezt Ć*rta:
hm seems like I have an other problem:
what I want to do: there's a column of values. I want to search the
first 4 letters of each cell in it, to know if it's equal to a given
value.
However, I have to do this 6 times. The first one would give the first
cell where it's in, the second one the second cell, etc..
A1: | abcd.. |
A2: | efgh.. |
A3: | abcd.. |
A4: | rstq.. |
A5: | abcd.. |
C1 would be something like:
Code:
--------------------
from A1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------
& would return 'A1'
C2 would be:
Code:
--------------------
from [content of C1]+1 to [end of A]
if cell[1-4] equals 'abcd'
echo cellname
quit
--------------------
& would return 'A3'
I just dont know how to do this in excel.
grtz & thx in advance
*update*
I think I can work something out with the MATCH-function in excel.
This, however, only given me the index of the 1st matching cell, while
I need ALL matching cells.
Is there one that will return a matrix?
--
naaitie
------------------------------------------------------------------------
naaitie's Profile: http://www.excelforum.com/member.php...o&userid=30184
View this thread: http://www.excelforum.com/showthread...hreadid=498657