Can i "filter" in excel by words in Bold?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger - 20 Dec 2005 20:26 GMT

What I want to do is to consecutively number a list of file names. These
files are in text boxes in column B. Each text box covers a varying number of
rows. So files names contain multiple text boxes. The distinguishing
feature is that the first line of a text box containing a new file is in
BOLD.
I am trying to filter for just the lines which contain words in BOLD so that
i can then number the files in Col A.

The second part of the question is:
Once i have the list filtered to hide the unwanted rows, is there a
way to use the automatic numbering feature of excel (ie number the first two
cell 1 and 2 then dragging through the rest of the cells to number the
others) and get it to only number the cells shown by the filter, to skip the
hidden rows.

Reply to this Message

Bernie Deitrick - 21 Dec 2005 14:42 GMT

It depends, on what you mean by "text boxes". Are you refering to cells, cells that are merged, or
to the drawing objects?

HTH,
Bernie
MS Excel MVP

> What I want to do is to consecutively number a list of file names. These
> files are in text boxes in column B. Each text box covers a varying number of
[quoted text clipped - 10 lines]
> others) and get it to only number the cells shown by the filter, to skip the
> hidden rows.

Reply to this Message

Roger - 22 Dec 2005 01:55 GMT

Thank you for taking time to reply.
Im afraid im going to show my lack of familiarity with excel here.
They are not typical cells, nor are they merged cells. I am not certain i
would call them drawing objects, as i am able to edit the text with in them.
The best description i can give is a text box. I crosses the lines of the
rows in the column in which is resides.
As you can probably tell, these are in files i did not create but which i
must edit.

Is that any better?

> It depends, on what you mean by "text boxes". Are you refering to cells, cells that are merged, or
> to the drawing objects?
[quoted text clipped - 17 lines]
> > others) and get it to only number the cells shown by the filter, to skip the
> > hidden rows.

Reply to this Message

Bernie Deitrick - 22 Dec 2005 14:18 GMT

Roger,

Shapes are bad :-( , at least for use in a worksheet. But we can get around that with a macro.
Run the macro below when the sheet with the rectangles is active, and see if it finds all of your
text boxes. And then let us know, and we'll go from there.

HTH,
Bernie
MS Excel MVP

Sub FindRogersTextBoxes()
Dim myShape As Object
Dim i As Long
Dim myLeftCol As Integer
Dim myRow As Long

For Each myShape In ActiveSheet.Shapes
For i = 1 To 255
If Columns(i).Left >= myShape.Left Then
myLeftCol = i - IIf(Columns(i).Left <> myShape.Left, 1, 0)
GoTo FoundCol
End If
Next i
FoundCol:
For i = 1 To Rows.Count
If Rows(i).Top >= myShape.Top Then
myRow = i - IIf(Rows(i).Top <> myShape.Top, 1, 0)
GoTo FoundRow
End If
Next i
FoundRow:
MsgBox myShape.Name & " contains the text: " & _
myShape.TextFrame.Characters.Text & Chr(10) & _
"And is located at " & _
Cells(myRow, myLeftCol).Address(False, False)
Next myShape

End Sub

> Thank you for taking time to reply.
> Im afraid im going to show my lack of familiarity with excel here.
[quoted text clipped - 29 lines]
>> > others) and get it to only number the cells shown by the filter, to skip the
>> > hidden rows.

Reply to this Message

Roger - 27 Dec 2005 23:05 GMT

Wow! thanks for taking the time to write a macro. i really appreciate it.
Unfortunately it did not work, because i gave u faulty information. Over
the holidays (hope yours were great by the way) i learned a few things. 1.
The column i am trying to sort by are merged cells as you originally asked.
2. i have been able to come close to sorting the way i want using the filter
function and filtering for Non-Blanks. This is not 100% as it filters by the
first line of each group of merged cells, and some of the things i am trying
to number are multiple groups. I can still tell which are which by the which
groups of merged cells are in bold.

Perhaps you can help me refine this?

Secondly, any ideas how to use the click and drag numbering to only number
the cells which show and not the cells which are hidden by the filter?

Once again, i really appreciate all you time and effort in helping me.

> Roger,
>
[quoted text clipped - 68 lines]
> >> > others) and get it to only number the cells shown by the filter, to skip the
> >> > hidden rows.

Reply to this Message

Bernie Deitrick - 28 Dec 2005 15:59 GMT

Second thing first: to enumerate only cells that are not hidden, you need to use a column of
formulas, along the lines of

=SUBTOTAL(3,$B$2:B2)

entered into row 2 and copied down to match your values. Then when you filter, these numbers will
update automatically.

As for the merged cells and the bold, I'm unclear on what you mean by "first line of each group of
merged cells"

But, anyeay, this user-defined-function can tell if something is bold. For example, copy this code
into a codemodule in your workbook:

Function isBold(myCell As Range, myChar As Integer) As Boolean
isBold = myCell.Characters(myChar, 1).Font.Bold
End Function

Then use it like so:

=IsBold(A1,2)

It will return TRUE if the second character of A1 is bold, and FALSE otherwise.

If that doesn;t work, you can post an example of what you have, and we'll go from there.

HTH,
Bernie
MS Excel MVP

> Wow! thanks for taking the time to write a macro. i really appreciate it.
> Unfortunately it did not work, because i gave u faulty information. Over
[quoted text clipped - 86 lines]
>> >> > others) and get it to only number the cells shown by the filter, to skip the
>> >> > hidden rows.

Reply to this Message