• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Excel code that copies bold text to bold text

Tpackard

New Member
Hi all,
I am relatively new to VBA and I am working on a project where I want my VBA code to find the bold text in a column and copy that bold text and the following text until coming upon the next bold text. Then take that text to a new sheet and transpose paste it into rows with the bold text being the first in the row. Here is an example of what I need:
1
2
3
4
5
6
7
8
vba code runs and result is:
1, 2, 3,4
5, 6, 7, 8
Any help would be greatly appreciated!

Edit: added example spreadsheet for reference
 

Attachments

Last edited:

p45cal

Well-Known Member
Attach a workbook with this in it and a mock-up sheet with how you'd like to see the results; it'll answer a lot of questions.
 

Tpackard

New Member
Chihiro, I don't need to keep the bold format the transposing is my main goal and to have the code recognize the bold print as a marker to make new row.
 
Last edited:

Chihiro

Excel Ninja
May be something like below.

Code:
Sub Demo()
Dim cel As Range
Dim x
With Sheet1
    For Each cel In .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Cells
        If cel.Font.Bold Then
            x = IIf(Len(x) = 0, cel.Row, x & "," & cel.Row)
        End If
    Next
    x = x & "," & .Cells(Rows.Count, "A").End(xlUp).Row + 1
    x = Split(x, ",")
    For i = 0 To UBound(x) - 1
        .Range(.Cells(x(i), 1), .Cells(x(i + 1) - 1, 1)).Copy
        lRow = Sheet2.Range("A1").CurrentRegion.Rows.Count + 1
        Sheet2.Range("A" & lRow).PasteSpecial Transpose:=True
    Next
End With

End Sub
 
Top