How do I find a cell starting with a specific letter?

[Guest]

I have a large worksheet contains rwos with name and address columns. it is
sorted in ascending order by the last name column and address. I want to
print a list of the names and addresses with page breaks at specific letters.
How can I locate the first row with a last name beginning with a specific
letter so that I can insert a page break? I know I can do this manually by
scrolling throught the list, finding the right row, selecting it and
inserting a page break. However, I would like to create a macro to insert
all the page breaks automatically.

 

[Biff]

Hi!

Doesn't help with wanting a macro but here's a formula that will do what you
want:

Assume last names are in the range B2:B200

In a cell, say, J1 enter the letter of the last name that you want to find.

Either one of these formulas entered as an array using the key combo of
CTRL,SHIFt,ENTER:

=MIN(IF(LEFT(B2:B200)=J1,ROW(2:200)))

=MATCH(J1,LEFT(B2:B200),0)+1

Biff

 

[Héctor Miguel]

hi, Bking !

... a large worksheet... name and address...sorted in ascending... by the last name... and address
... print a list of the names and addresses with page breaks at specific letters
... locate the first row with a last name beginning with a specific letter so that I can insert a page break?
... I would like to create a macro to insert all the page breaks automatically.

you might want to give a try with the following code
starting at row 3, assuming rows 1 and 2 are title and the first letter... no pagebreak is needed -?-
please note that Like [text-comparison-operator] is case-sensitive, so... play with the specific letters you need.

hth,
hector.

Sub BreakEveryLetter()
Application.ScreenUpdating = False
Dim nRow As Long
With ActiveSheet
On Error Resume Next
For nRow = 1 To .HPageBreaks.Count
.HPageBreaks(1).Delete
Next
On Error GoTo 0
For nRow = 3 To Range("a65536").End(xlUp).Row
If Left(Cells(nRow, 1), 1) Like "[BDF]" _
And Left(Cells(nRow, 1), 1) <> Left(Cells(nRow - 1, 1), 1) _
Then .HPageBreaks.Add Rows(nRow)
Next
End With
End Sub

 

[Guest]

Thanks Biff.

The first formula works. The second formula returned #Value!.

I created a column for the first formula. When I change the value in J1,
the first row in that column with a value >0 is the letter in J1.

Now if I can only figure out how to incorporate one of these nto a macro.

 

[Bob Phillips]

That is because it is an array formula, so you commit it with
Ctrl-Shift-Enter, not just Enter.

Actually, to be of any use to you to replicate for other letters, the table
should be absolute. When you do this, you will see the formula surrounded by
{...} in the formula bar. Excel inserts these, you do not need to type them
in.

=MATCH(J1,LEFT($B$2:$B$200),0)+1

 

[Guest]

Sorry for the delay in responding. I had another project that I needed to
complete. I tried this code, but it looks like nothing happens to the
worksheet. I understand most of what the code is trying to do.

I added several page breaks to theworksheet and ran the code. The page
breaks I added were gone. But none were added. I did change the Range to
b65536 since column b is the column I am keying off of for the page breaks.

One thing I have to do before printing is to filter for non-blanks in col A.
But I ran the code before doing the filter as well as after filtering.
Still no page breaks.

Any ideas what I might be doing wrong?

Thank you.
Bob

hi, Bking !

... a large worksheet... name and address...sorted in ascending... by the last name... and address
... print a list of the names and addresses with page breaks at specific letters
... locate the first row with a last name beginning with a specific letter so that I can insert a page break?
... I would like to create a macro to insert all the page breaks automatically.

you might want to give a try with the following code
starting at row 3, assuming rows 1 and 2 are title and the first letter... no pagebreak is needed -?-
please note that Like [text-comparison-operator] is case-sensitive, so... play with the specific letters you need.

hth,
hector.

Sub BreakEveryLetter()
Application.ScreenUpdating = False
Dim nRow As Long
With ActiveSheet
On Error Resume Next
For nRow = 1 To .HPageBreaks.Count
.HPageBreaks(1).Delete
Next
On Error GoTo 0
For nRow = 3 To Range("a65536").End(xlUp).Row
If Left(Cells(nRow, 1), 1) Like "[BDF]" _
And Left(Cells(nRow, 1), 1) <> Left(Cells(nRow - 1, 1), 1) _
Then .HPageBreaks.Add Rows(nRow)
Next
End With
End Sub