software

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.

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

HTH,
Bernie
MS Excel MVPquot;Rogerquot; gt; wrote in message
...
gt; What I want to do is to consecutively number a list of file names. These
gt; files are in text boxes in column B. Each text box covers a varying number of
gt; rows. So files names contain multiple text boxes. The distinguishing
gt; feature is that the first line of a text box containing a new file is in
gt; BOLD.
gt; I am trying to filter for just the lines which contain words in BOLD so that
gt; i can then number the files in Col A.
gt;
gt; The second part of the question is:
gt; Once i have the list filtered to hide the unwanted rows, is there a
gt; way to use the automatic numbering feature of excel (ie number the first two
gt; cell 1 and 2 then dragging through the rest of the cells to number the
gt; others) and get it to only number the cells shown by the filter, to skip the
gt; hidden rows.
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?

quot;Bernie Deitrickquot; wrote:

gt; It depends, on what you mean by quot;text boxesquot;. Are you refering to cells, cells that are merged, or
gt; to the drawing objects?
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;Rogerquot; gt; wrote in message
gt; ...
gt; gt; What I want to do is to consecutively number a list of file names. These
gt; gt; files are in text boxes in column B. Each text box covers a varying number of
gt; gt; rows. So files names contain multiple text boxes. The distinguishing
gt; gt; feature is that the first line of a text box containing a new file is in
gt; gt; BOLD.
gt; gt; I am trying to filter for just the lines which contain words in BOLD so that
gt; gt; i can then number the files in Col A.
gt; gt;
gt; gt; The second part of the question is:
gt; gt; Once i have the list filtered to hide the unwanted rows, is there a
gt; gt; way to use the automatic numbering feature of excel (ie number the first two
gt; gt; cell 1 and 2 then dragging through the rest of the cells to number the
gt; gt; others) and get it to only number the cells shown by the filter, to skip the
gt; gt; hidden rows.
gt;
gt;
gt;

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 MVPSub 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 gt;= myShape.Left Then
myLeftCol = i - IIf(Columns(i).Left lt;gt; myShape.Left, 1, 0)
GoTo FoundCol
End If
Next i
FoundCol:
For i = 1 To Rows.Count
If Rows(i).Top gt;= myShape.Top Then
myRow = i - IIf(Rows(i).Top lt;gt; myShape.Top, 1, 0)
GoTo FoundRow
End If
Next i
FoundRow:
MsgBox myShape.Name amp; quot; contains the text: quot; amp; _
myShape.TextFrame.Characters.Text amp; Chr(10) amp; _
quot;And is located at quot; amp; _
Cells(myRow, myLeftCol).Address(False, False)
Next myShape

End Subquot;Rogerquot; gt; wrote in message
...
gt; Thank you for taking time to reply.
gt; Im afraid im going to show my lack of familiarity with excel here.
gt; They are not typical cells, nor are they merged cells. I am not certain i
gt; would call them drawing objects, as i am able to edit the text with in them.
gt; The best description i can give is a text box. I crosses the lines of the
gt; rows in the column in which is resides.
gt; As you can probably tell, these are in files i did not create but which i
gt; must edit.
gt;
gt; Is that any better?
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; It depends, on what you mean by quot;text boxesquot;. Are you refering to cells, cells that are merged,
gt;gt; or
gt;gt; to the drawing objects?
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt;
gt;gt; quot;Rogerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; What I want to do is to consecutively number a list of file names. These
gt;gt; gt; files are in text boxes in column B. Each text box covers a varying number of
gt;gt; gt; rows. So files names contain multiple text boxes. The distinguishing
gt;gt; gt; feature is that the first line of a text box containing a new file is in
gt;gt; gt; BOLD.
gt;gt; gt; I am trying to filter for just the lines which contain words in BOLD so that
gt;gt; gt; i can then number the files in Col A.
gt;gt; gt;
gt;gt; gt; The second part of the question is:
gt;gt; gt; Once i have the list filtered to hide the unwanted rows, is there a
gt;gt; gt; way to use the automatic numbering feature of excel (ie number the first two
gt;gt; gt; cell 1 and 2 then dragging through the rest of the cells to number the
gt;gt; gt; others) and get it to only number the cells shown by the filter, to skip the
gt;gt; gt; hidden rows.
gt;gt;
gt;gt;
gt;gt;
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.

quot;Bernie Deitrickquot; wrote:

gt; Roger,
gt;
gt; Shapes are bad :-( , at least for use in a worksheet. But we can get around that with a macro.
gt; Run the macro below when the sheet with the rectangles is active, and see if it finds all of your
gt; text boxes. And then let us know, and we'll go from there.
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; Sub FindRogersTextBoxes()
gt; Dim myShape As Object
gt; Dim i As Long
gt; Dim myLeftCol As Integer
gt; Dim myRow As Long
gt;
gt; For Each myShape In ActiveSheet.Shapes
gt; For i = 1 To 255
gt; If Columns(i).Left gt;= myShape.Left Then
gt; myLeftCol = i - IIf(Columns(i).Left lt;gt; myShape.Left, 1, 0)
gt; GoTo FoundCol
gt; End If
gt; Next i
gt; FoundCol:
gt; For i = 1 To Rows.Count
gt; If Rows(i).Top gt;= myShape.Top Then
gt; myRow = i - IIf(Rows(i).Top lt;gt; myShape.Top, 1, 0)
gt; GoTo FoundRow
gt; End If
gt; Next i
gt; FoundRow:
gt; MsgBox myShape.Name amp; quot; contains the text: quot; amp; _
gt; myShape.TextFrame.Characters.Text amp; Chr(10) amp; _
gt; quot;And is located at quot; amp; _
gt; Cells(myRow, myLeftCol).Address(False, False)
gt; Next myShape
gt;
gt; End Sub
gt;
gt;
gt; quot;Rogerquot; gt; wrote in message
gt; ...
gt; gt; Thank you for taking time to reply.
gt; gt; Im afraid im going to show my lack of familiarity with excel here.
gt; gt; They are not typical cells, nor are they merged cells. I am not certain i
gt; gt; would call them drawing objects, as i am able to edit the text with in them.
gt; gt; The best description i can give is a text box. I crosses the lines of the
gt; gt; rows in the column in which is resides.
gt; gt; As you can probably tell, these are in files i did not create but which i
gt; gt; must edit.
gt; gt;
gt; gt; Is that any better?
gt; gt;
gt; gt; quot;Bernie Deitrickquot; wrote:
gt; gt;
gt; gt;gt; It depends, on what you mean by quot;text boxesquot;. Are you refering to cells, cells that are merged,
gt; gt;gt; or
gt; gt;gt; to the drawing objects?
gt; gt;gt;
gt; gt;gt; HTH,
gt; gt;gt; Bernie
gt; gt;gt; MS Excel MVP
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Rogerquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; What I want to do is to consecutively number a list of file names. These
gt; gt;gt; gt; files are in text boxes in column B. Each text box covers a varying number of
gt; gt;gt; gt; rows. So files names contain multiple text boxes. The distinguishing
gt; gt;gt; gt; feature is that the first line of a text box containing a new file is in
gt; gt;gt; gt; BOLD.
gt; gt;gt; gt; I am trying to filter for just the lines which contain words in BOLD so that
gt; gt;gt; gt; i can then number the files in Col A.
gt; gt;gt; gt;
gt; gt;gt; gt; The second part of the question is:
gt; gt;gt; gt; Once i have the list filtered to hide the unwanted rows, is there a
gt; gt;gt; gt; way to use the automatic numbering feature of excel (ie number the first two
gt; gt;gt; gt; cell 1 and 2 then dragging through the rest of the cells to number the
gt; gt;gt; gt; others) and get it to only number the cells shown by the filter, to skip the
gt; gt;gt; gt; hidden rows.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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 quot;first line of each group of
merged cellsquot;

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 MVPquot;Rogerquot; gt; wrote in message
...
gt; Wow! thanks for taking the time to write a macro. i really appreciate it.
gt; Unfortunately it did not work, because i gave u faulty information. Over
gt; the holidays (hope yours were great by the way) i learned a few things. 1.
gt; The column i am trying to sort by are merged cells as you originally asked.
gt; 2. i have been able to come close to sorting the way i want using the filter
gt; function and filtering for Non-Blanks. This is not 100% as it filters by the
gt; first line of each group of merged cells, and some of the things i am trying
gt; to number are multiple groups. I can still tell which are which by the which
gt; groups of merged cells are in bold.
gt;
gt; Perhaps you can help me refine this?
gt;
gt; Secondly, any ideas how to use the click and drag numbering to only number
gt; the cells which show and not the cells which are hidden by the filter?
gt;
gt; Once again, i really appreciate all you time and effort in helping me.
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; Roger,
gt;gt;
gt;gt; Shapes are bad :-( , at least for use in a worksheet. But we can get around that with a macro.
gt;gt; Run the macro below when the sheet with the rectangles is active, and see if it finds all of your
gt;gt; text boxes. And then let us know, and we'll go from there.
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt;
gt;gt; Sub FindRogersTextBoxes()
gt;gt; Dim myShape As Object
gt;gt; Dim i As Long
gt;gt; Dim myLeftCol As Integer
gt;gt; Dim myRow As Long
gt;gt;
gt;gt; For Each myShape In ActiveSheet.Shapes
gt;gt; For i = 1 To 255
gt;gt; If Columns(i).Left gt;= myShape.Left Then
gt;gt; myLeftCol = i - IIf(Columns(i).Left lt;gt; myShape.Left, 1, 0)
gt;gt; GoTo FoundCol
gt;gt; End If
gt;gt; Next i
gt;gt; FoundCol:
gt;gt; For i = 1 To Rows.Count
gt;gt; If Rows(i).Top gt;= myShape.Top Then
gt;gt; myRow = i - IIf(Rows(i).Top lt;gt; myShape.Top, 1, 0)
gt;gt; GoTo FoundRow
gt;gt; End If
gt;gt; Next i
gt;gt; FoundRow:
gt;gt; MsgBox myShape.Name amp; quot; contains the text: quot; amp; _
gt;gt; myShape.TextFrame.Characters.Text amp; Chr(10) amp; _
gt;gt; quot;And is located at quot; amp; _
gt;gt; Cells(myRow, myLeftCol).Address(False, False)
gt;gt; Next myShape
gt;gt;
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; quot;Rogerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Thank you for taking time to reply.
gt;gt; gt; Im afraid im going to show my lack of familiarity with excel here.
gt;gt; gt; They are not typical cells, nor are they merged cells. I am not certain i
gt;gt; gt; would call them drawing objects, as i am able to edit the text with in them.
gt;gt; gt; The best description i can give is a text box. I crosses the lines of the
gt;gt; gt; rows in the column in which is resides.
gt;gt; gt; As you can probably tell, these are in files i did not create but which i
gt;gt; gt; must edit.
gt;gt; gt;
gt;gt; gt; Is that any better?
gt;gt; gt;
gt;gt; gt; quot;Bernie Deitrickquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; It depends, on what you mean by quot;text boxesquot;. Are you refering to cells, cells that are
gt;gt; gt;gt; merged,
gt;gt; gt;gt; or
gt;gt; gt;gt; to the drawing objects?
gt;gt; gt;gt;
gt;gt; gt;gt; HTH,
gt;gt; gt;gt; Bernie
gt;gt; gt;gt; MS Excel MVP
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Rogerquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; What I want to do is to consecutively number a list of file names. These
gt;gt; gt;gt; gt; files are in text boxes in column B. Each text box covers a varying number of
gt;gt; gt;gt; gt; rows. So files names contain multiple text boxes. The distinguishing
gt;gt; gt;gt; gt; feature is that the first line of a text box containing a new file is in
gt;gt; gt;gt; gt; BOLD.
gt;gt; gt;gt; gt; I am trying to filter for just the lines which contain words in BOLD so that
gt;gt; gt;gt; gt; i can then number the files in Col A.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; The second part of the question is:
gt;gt; gt;gt; gt; Once i have the list filtered to hide the unwanted rows, is there a
gt;gt; gt;gt; gt; way to use the automatic numbering feature of excel (ie number the first two
gt;gt; gt;gt; gt; cell 1 and 2 then dragging through the rest of the cells to number the
gt;gt; gt;gt; gt; others) and get it to only number the cells shown by the filter, to skip the
gt;gt; gt;gt; gt; hidden rows.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;