Excel: Set part of each cell to BOLD 1

[GPerk]

I have a concordance file with tens of thousands of names to be used in creating an index. It is in the form
A B
JOHN DOE Doe : John
JANE JONES Jones : Jane

I want to change the last names in column B to Bold font.
Can this be done? Using a Macro?
GPerk

 

[Zathras]

Without getting too fancy here is one way to do it:
[blue]

Option Explicit

Sub BoldAllLastNames()
Dim rng As Range
Dim c As Range
Dim nLen As Integer
  Set rng = Range("B2:B10")

[green]

 '<---Set range here

[/color]

  Application.ScreenUpdating = False
  For Each c In rng
    nLen = InStr(c.Text, " : ")

[green]

 '<---Set delimiter here

[/color]

    If nLen > 0 Then
      c.Characters(1, nLen).Font.FontStyle = "Bold"
    End If
  Next c
  Application.ScreenUpdating = True
  Set rng = Nothing
End Sub

[/color]

 

[bryanbayfield]

Hi GPerk, which application are you in?

I'm guessing it's not Excel since the maximum number of records you can have per worksheet if 16,000 odd, and you say you have tens of thousands ...

 

[xlbo]

Bryan - you 'bin sitting in the sun too long ???
65000 would be the apprx number of rows in 97 onwards and 32000 in 95....

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GPerk]

I am using Excel 2000 (9.0).
The limit is 65535 rows per sheet. I have about 80,000 records.
Excel tells me that its Wizard will help me load the remainder into the second sheet. But I can't get that to work. What I'm doing is loading the first 65535 in sheet 1; noting the name on the last record; deleting those from the source file; and loading the rest into sheet 2.
It would probably be easier to just split the file into two equal parts and load those. . .
GPerk

 

[bryanbayfield]

Hmmm ... been at Matron's drinks cabinet again - naughty Bryan!

I've just tried opening a text stream file for input for the first time - it seems to parse on commas. For what it's worth, I'd probably take splitting teh file in two as the easy way. Even if you used VBA to read the file in, then you'd have to construct a loop which would take into account the number of rows on each sheet and put the values in accordingly - quite possibly more trouble than it's worth.

 

[Zathras]

As long as you have to use multiple sheets, I would suggest breaking it up into more than 2, and use the tab to help the user to find the index page that is wanted. For example: A-G, H-M, N-R, S-Z

 

[bryanbayfield]

Depending upon how time critical your macro is (you do have a lot of records), this refinement maybe of use.

It parses the cell in question into a colon delimited array, takes the length of the first member of the array (corresponding to the person's surname) so that the number of characters to make bold can be ascertained.

Dim c As Range
ActiveSheet.UsedRange
On Error Resume Next
For Each c In ActiveSheet.UsedRange.Columns(2).Cells
    c.Characters(1, Len(Split(c.Value, ":")(0))).Font.FontStyle = "Bold"
    Next

:

 

[mscallisto]

GPerk

With about 80,000 records is there a reason you can't use Access?

Excel is technically a "spreadsheet" thus limited amout of rows, while Access is technically the database.

 

[GPerk]

mscallisto

No technical reason.
It's just the the user is familiar with Word and Excel and is not interested in using Access.

Basically, what the user is doing is this:
He writes books with a lot of genealogy info in them. The genealogy is generated by Family Tree Maker. He then merges the genealogy with his other text using Word. Word can generate an index if you (1) flag every name in the text and also type in the name as it should appear in the index (too much work!), or (2) give Word a concordance file.
The names in the book are all upper case. He wants the index to be lower case.
He can get a list from FTM containing all 80,000 names in Lastname, Firstname format which he can easily load into Excel.
The concordance file must be in this format:
A B
JOHN DOE Doe : John
JANE JONES Jones : Jane

So, my VBA takes the Excel list that came from FTM and puts it in the concordance format. Then Word uses that to generate the index with page numbers.
No, this isn't the quickest, easiest way to do this from my perspective, but it saves the user tons of work and he is happy to do it that way.

 

[mscallisto]

Ahh now I understand and agree with your methodology.

Splitting the file then applying the Zathras "Bolding" code in conjunction with your code is a very good solution.

Do you know if Word will work with your concordance file if it spans more than one sheet?

 

[GPerk]

mscallisto
Yes, it will.
The concordance file is just one long list of name in the format:
JOHN DOE (tab) Doe:John (tab)
It can be created by concatenating several files if necessary.

 

[mscallisto]

Thanks kindly...thats nice to know.

Much luck