+ Reply to Thread
Results 1 to 13 of 13

VBA Selecting all bold rows and inserting a copied row under it

Hybrid View

stupsi01 VBA Selecting all bold rows... 12-29-2016, 03:31 AM
kasan Re: VBA Selecting all bold... 12-29-2016, 04:05 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 04:12 AM
kasan Re: VBA Selecting all bold... 12-29-2016, 04:30 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 05:06 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 05:21 AM
kasan Re: VBA Selecting all bold... 12-29-2016, 05:33 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 05:56 AM
kasan Re: VBA Selecting all bold... 12-29-2016, 06:06 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 06:13 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 09:51 AM
kasan Re: VBA Selecting all bold... 12-29-2016, 10:40 AM
stupsi01 Re: VBA Selecting all bold... 12-29-2016, 10:48 AM
  1. #1
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Question VBA Selecting all bold rows and inserting a copied row under it

    Hi, I'm new to VBA and I was looking for a code so select all bold rows (some titles) in my Sheet. Then, it should insert a earlier copied row underneath it and shift everything down. I have this code but Excel is giving me the error that I can't do a multiple choice selection:


    Sub Test()
    
    'Select 2nd Row and Copy it
    Rows("2:2").Select
    Selection.Copy
    
    'Select Bold Rows
        Dim rngA As Range
        Dim cell As Range
        Dim SpecialSelect As Range
         
        Set rngA = Range("$A$1:$K$2000")
        For Each cell In rngA
            If cell.Font.Bold = True Then
                If SpecialSelect Is Nothing Then
                    Set SpecialSelect = cell.EntireRow
                Else
                    Set SpecialSelect = Union(SpecialSelect, cell.EntireRow)
                End If
            End If
        Next cell
         
        If Not SpecialSelect Is Nothing Then
            SpecialSelect.Select
        End If
        
    'Insert earlier copied row
        Selection.Insert shift:=xlDown
         
    End Sub
    Thanks in advance for your help!
    Last edited by stupsi01; 12-29-2016 at 11:21 AM.

  2. #2
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Selecting all bold rows and inserting a copied row under it

    What really do you want to get? You could upload small sample to show disired result.
    Lets say row 2 ir "1234"
    Column A looks like:
    Text1
    Text2
    Text3
    Text4

    After you run your code result might be ... ?

  3. #3
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    Start would be this:

    HI
    Copy this
    that
    is
    a
    test
    Whats
    up
    Hello


    result should be like this:
    Unbenannt.PNG

    So each time there's a bold row it should copy the 2nd Row down there and shift the rest down.

  4. #4
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Selecting all bold rows and inserting a copied row under it

    You make it too complicate, I think.
    If you need find rows with text in bold, so search just in one column (if all values in a row is in bold, so value in column A also should be in bold).
    Insert your value from row 2 just after bold row is found.
    Try this.
    Sub Test()
    Dim i As Long
    
    For i = 2 To 2000
        If Range("A" & i).Font.Bold Then
            Rows(i + 1).Insert shift:=xlShiftUp
            Range("A" & i + 1).Clear
            Range("A" & i + 1).Value = Range("A2").Value
        End If
    Next
    
    End Sub

  5. #5
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    Hi kasan, thanks for your help. Excuse me, I did not get it right.
    The macro can search just in column A, because i have nothing else bold there. BUT it should copy all Titles from A:K . Have a look at (a better) example: (The example one is only from A:C, I need it from A:K)
    Unbenannt.PNG

    If I modify the code to choose A:K it doesn't work anymore.

  6. #6
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    OK, just tested a little bit and found out. I just copied the rest of the rows down:

    Sub Test()
    Dim i As Long
    
    For i = 2 To 2000
        If Range("A" & i).Font.Bold Then
            Rows(i + 1).Insert shift:=xlShiftUp
            Range("A" & i + 1).Clear
            Range("B" & i + 1).Clear
            Range("C" & i + 1).Clear
            Range("D" & i + 1).Clear
            Range("E" & i + 1).Clear
            Range("F" & i + 1).Clear
            Range("G" & i + 1).Clear
            Range("H" & i + 1).Clear
            Range("I" & i + 1).Clear
            Range("J" & i + 1).Clear
            Range("K" & i + 1).Clear
            Range("A" & i + 1).Value = Range("A2").Value
            Range("B" & i + 1).Value = Range("B2").Value
            Range("C" & i + 1).Value = Range("C2").Value
            Range("D" & i + 1).Value = Range("D2").Value
            Range("E" & i + 1).Value = Range("E2").Value
            Range("F" & i + 1).Value = Range("F2").Value
            Range("G" & i + 1).Value = Range("G2").Value
            Range("H" & i + 1).Value = Range("H2").Value
            Range("I" & i + 1).Value = Range("I2").Value
            Range("J" & i + 1).Value = Range("J2").Value
            Range("K" & i + 1).Value = Range("K2").Value
        End If
    Next
    
    End Sub
    Now I have another problem. The formatting is incorrect. All of my Titles are in Calibri 9, bold. The copied titles are Calibri 11, not bold
    Last edited by stupsi01; 12-29-2016 at 05:31 AM.

  7. #7
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Selecting all bold rows and inserting a copied row under it

    Hmm, and those Title1, Title2.. you insert with other macro, right?
    Why don't you insert titles Name, Address, Country just after you manage Title1?

  8. #8
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    I didn't manage to do that. I used this code to create the titles:



    Dim i As Long
    
    For i = 1 To Sheets("Sheet1").Cells(Sheets("r").Rows.Count, "A").End(xlUp).Row
        If Sheets("Sheet1").Range("D" & i) <> Sheets("Sheet1").Range("D" & i + 1) Then
            Rows(i + 1).Insert shift:=xlShiftDown
            Sheets("Sheet1").Range("A" & i + 1).Value = Sheets("Sheet1").Range("D" & i + 2).Value
    
            With Sheets("Sheet1").Range("A" & i + 1)
                .Font.Size = 10
                .Font.Name = "Calibri"
                .Font.Bold = True
                .Font.Italic = False
                .HorizontalAlignment = xlCenter
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
            End With
            Range(Cells(i + 1, 1), Cells(i + 1, 11)).Merge
            i = i + 1
        End If
    Next

  9. #9
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Selecting all bold rows and inserting a copied row under it

    What about this
    Dim i As Long
    
    For i = 1 To Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
        If Sheets("Sheet1").Range("D" & i) <> Sheets("Sheet1").Range("D" & i + 1) Then
            Rows(i + 1).Insert shift:=xlShiftDown
            Sheets("Sheet1").Range("A" & i + 1).Value = Sheets("Sheet1").Range("D" & i + 2).Value
    
            With Sheets("Sheet1").Range("A" & i + 1)
                .Font.Size = 10
                .Font.Name = "Calibri"
                .Font.Bold = True
                .Font.Italic = False
                .HorizontalAlignment = xlCenter
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
            End With
            Range(Cells(i + 1, 1), Cells(i + 1, 11)).Merge
            
            'insert row for titles
            Rows(i + 2).Insert shift:=xlShiftDown
            Range("A" & i + 2).Value = "Name"
            Range("B" & i + 2).Value = "Address"
            'add all you want
                    
            i = i + 2
        End If
    Next

  10. #10
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    ,,,,,,,,,,
    Last edited by stupsi01; 12-29-2016 at 09:52 AM.

  11. #11
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    How can I now insert a Page Break before the titles? It doesn't work like this:

    blabla        
    Rows(i + 2).Insert shift:=xlShiftDown
    Range("A" & i + 2).Value = "Name"
    Range("B" & i + 2).Value = "Second Name"
    Range("C" & i + 2).Value = "Address"
    Range("D" & i + 2).Value = "Street"
    Range("E" & i + 2).Value = "State"
    Range("F" & i + 2).Value = "House"
    Range("G" & i + 2).Value = "Room"
    Range("H" & i + 2).Value = "Date"
    Range("I" & i + 2).Value = "Something"
    Range("J" & i + 2).Value = "Anotherone"
    Range("K" & i + 2).Value = "blabla"
    
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    Could this possibly be because the title is at this moment not an "ActiveCell"?
    Last edited by stupsi01; 12-29-2016 at 09:56 AM.

  12. #12
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Selecting all bold rows and inserting a copied row under it

    Probably
    ActiveSheet.HPageBreaks.Add Before:=Rows(i+2)
    Test it and if necessary play with that i+2 (may be i+1 will give you correct result)

  13. #13
    Registered User
    Join Date
    12-27-2016
    Location
    Switzerland
    MS-Off Ver
    2010, Office 365
    Posts
    26

    Re: VBA Selecting all bold rows and inserting a copied row under it

    Thx, never thought of writing this directly into the same code.. It works perfectly with
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(i + 1)
    as you said

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 08-16-2014, 01:27 PM
  2. Inserting copied rows (some locked cells, some not) in a protected sheet
    By kmartinez0180 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2014, 08:09 PM
  3. Ask for save before close and allow inserting copied rows
    By G.Bregvadze in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-07-2013, 02:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1