SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - common macro ???

(7 posts)
  • Started 1 year ago by 6card
  • Latest reply from 6card
  • Topic Viewed 575 times

6card
6card
Posts: 357

I use a contact list with several hundred entries.

Would like a macro to search for, select and color high-light all listed entries in this list, ie, search for by first letter of last name. e.g., find all entries with a particular first letter … all Cs.

sixcard

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

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?

Posted 1 year ago
Top
 
whs
whs
Posts: 17584

If you put e.g. b* (b asterisk) into the search field, you get every name that contains a 'b' - but not only as first letter.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

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.

Posted 1 year ago
Top
 
6card
6card
Posts: 357

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

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

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

Posted 1 year ago
Top
 
6card
6card
Posts: 357

@moregg,

Yikes, I'm not quite that whack-o, but I will save that code.

Many thanks for you contribution to this thread.

sixcard

will mark solved

Posted 1 year ago
Top
 



Topic Closed

This topic has been closed to new replies.