How-To Geek Forums / Microsoft Office
(Solved) - common macro ???
(7 posts)- Remove Solved Status
Hi 6card
Is highlighting a selected first letter of the last name the only condition you would want?
Is the Last Name in its own column? i.e. is there a column for First name and another for Last name?
Which column is the last name in?
Also, just for discussion, most email contact lists would naturally have this capability built in. Wouldn't that be a better option?
Here's a quick macro that uses the in-built Filter function. It assumes the Last Name is in Col A and that you will only filter on the first letter.
Sub SpecialFilter()
'
' Macro1 SpecialFilter
'
Res = InputBox("Enter a letter to filter on (leave blank to see all)", "Filter Critera")
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$33").AutoFilter Field:=1, Criteria1:=Res & "*", _
Operator:=xlAnd
End Sub
This will need further work based on your layout but will give you an indication of how it will work.
Thanks moregg and WHS for the prompt reply,
The last code did what I need. And it negates my need to select and hi-lite. However it hides and locks up all other listings (rows) except the "Bs" (before and after B) and the first A. I can get back to the beginning by searching for A but, there is no way I can unlock the hidden rows. I tried to re height all but that does not work. I did a work around by creating a duplicate sheet.
BTW ... Your question ... "Also, just for discussion, most email contact lists would naturally have this capability built in. Wouldn't that be a better option?"
[Yes, I realize that but I like my own list. I'm a bit whacko in that I like my own pillow, my own Scotch, my own Mini Cooper, my own wife, etc.]
six
Hi 6card
The reason it always shows the first A is that the code expects there to be column headers e.g. "Last Name", "First Name", "Address", "City" etc.
To unlock all cells run the Macro again but leave the input blank.
However, in order to conform with your whacko-iness here is code that will highlight the selections (you can put in as many characters as you want so if you are looking for a particular name you can enter the full last name)
Sub Highlight()
'
Application.ScreenUpdating = False
Res = InputBox("Enter a letter to filter on (leave blank to see all)", "Filter Critera")
Q = """"
NChars = Len(Res)
LRow = Range("A2").End(xlDown).Row
Range("A1:M" & LRow).Select
Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEFT($A1," & NChars & ")=" & Q & Res & Q
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
End Sub
Topic Closed
This topic has been closed to new replies. Please create a new topic instead.
