Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can i "filter" in excel by words in Bold?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can i "filter" in excel by words in Bold?
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 "Roger" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can i "filter" in excel by words in Bold?
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? "Bernie Deitrick" wrote: 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 "Roger" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can i "filter" in excel by words in Bold?
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 "Roger" wrote in message ... 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? "Bernie Deitrick" wrote: 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 "Roger" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can i "filter" in excel by words in Bold?
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. "Bernie Deitrick" wrote: 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 "Roger" wrote in message ... 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? "Bernie Deitrick" wrote: 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 "Roger" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can i "filter" in excel by words in Bold?
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 "Roger" wrote in message ... 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. "Bernie Deitrick" wrote: 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 "Roger" wrote in message ... 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? "Bernie Deitrick" wrote: 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 "Roger" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
excel will not bold a cell, then program quits | Excel Worksheet Functions | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
how do I add accents for foreign words when using excel? | Excel Discussion (Misc queries) |