Selecting cells with same formatting

[psaulm119]

I have a spreadsheet that I keep with a variety of text items. I use this when making an exam, and I only want to select some of the items to be on the test. I typically make my choices by applying boldprint formatting. Is there a way to select all instances in a worksheet with boldprint formatting? I believe I have seen a download/add-on for Excel that allows this, but I'd rather just learn to do it manually if its not too much trouble, and if its possible.

 

[VoG]

Try

Sub SelectBold()
Dim c As Range, r As Range
For Each c In ActiveSheet.UsedRange
    If c.Font.Bold Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
    End If
Next c
If Not r Is Nothing Then r.Select
End Sub

 

[psaulm119]

VoG, thanks for your code. I tried this, but after running the macro, I was told that I couldn't copy the selected text.

You see, what I want to do is copy the selected text, then paste it (unformatted text) onto a Word doc.

Is there a macro that will allow me to do that?

 

[VoG]

You cannot copy multiple selections. What you can do is copy one area at a time and paste it into a contiguous range elsewhere on the sheet. For example

Sub SelectBold()
Dim c As Range, r As Range, Area As Range
For Each c In ActiveSheet.UsedRange
    If c.Font.Bold Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
    End If
Next c
If Not r Is Nothing Then
    For Each Area In r.SpecialCells(xlCellTypeConstants)
        Area.Copy Destination:=Range("H" & Rows.Count).End(xlUp).Offset(1)
    Next Area
End If
End Sub

 

[psaulm119]

Great. I think that works. For any who want to use this code, if you look you can see that it will automatically paste a column with all of the worksheet's boldprint text onto a new column, H. This was a bit awkward, as occasionally I have text in column H, so I changed the macro code to paste it in column A (all I did was edit the macro, and change uppercase H to uppercase A).

Works like a charm. I only have 4 spreadsheets that I use constantly for this, so I'll save this macro in each of those spreadsheets.

Thanks again.

You cannot copy multiple selections. What you can do is copy one area at a time and paste it into a contiguous range elsewhere on the sheet. For example

Sub SelectBold()
Dim c As Range, r As Range, Area As Range
For Each c In ActiveSheet.UsedRange
    If c.Font.Bold Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
    End If
Next c
If Not r Is Nothing Then
    For Each Area In r.SpecialCells(xlCellTypeConstants)
        Area.Copy Destination:=Range("H" & Rows.Count).End(xlUp).Offset(1)
    Next Area
End If
End Sub