Results 1 to 6 of 6

Thread: Insert row question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert row question

    If I want to add/insert the name Mary (after each Larry) to the Excel column below, without typing in Mary about 10 or more times, what menu would I use?

    Bobby
    Doris
    Jane
    Joe
    Larry
    Bobby
    Doris
    Jane
    Joe
    Larry
    Bobby
    Doris
    Jane
    Joe
    Larry
    Bobby
    Doris
    Jane
    Joe
    Larry
    Bobby
    Doris
    Jane
    Joe
    Larry
    Bobby
    Doris
    Jane
    Joe
    Larry
    Last edited by Bernard2; Sep 21st, 2019 at 10:48 AM.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert row question

    If you don't want to use VBA, here's a "menu" approach that will save some keystrokes.

    1. Using the ctrl key, select all the cells that come immediately after each Larry.
    2. Release the ctrl key, right-click and choose Insert>Shift cells down.
    3. Press F2 key and type: Mary.
    4.Hold down ctrl key and press Enter.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert row question

    Well, I guess, there's no Excel menu I can use?
    Last edited by Bernard2; Sep 21st, 2019 at 07:27 PM.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Insert row question

    Quote Originally Posted by Bernard2 View Post
    Well, I guess, there's no Excel menu I can use?
    Step 2 uses the right-click context menu. If you're looking for a single click solution you'll need VBA.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,241
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Insert row question

    For those who might be interested in seeing a VBA macro solution, this one will work so long as the final outputted range is less than 65500+ rows...
    Code:
    Sub AddMaryAfterLarry()
      Dim Arr As Variant
      Arr = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
      Arr = Split(Replace(Join(Arr, Chr(1)), "Larry", "Larry" & Chr(1) & "Mary"), Chr(1))
      Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Insert row question

    Quote Originally Posted by Bernard2 View Post
    Well, I guess, there's no Excel menu I can use?
    Your use of the term menu seems a little unusual in this context. There is no menu item (the menus are those things listed on or above the ribbon at the top of the sheet, or right click as mentioned by JoeMo) that will do this task directly.
    Perhaps you mean a formula solution or a macro solution?

    Rick has given you a macro solution so here is a formula solution. I have assumed a heading at the top (you could always insert a new row for this if required)
    1. Ensure the same heading in B1 as A1
    2. Put the formula shown in B2 and copy down until blank cells start appearing
    3. Copy the new column of names, select A2 and Paste Special -> Values -> OK
    4. Delete column B

    Insert Mary

    AB
    1ListList
    2BobbyBobby
    3DorisDoris
    4JaneJane
    5JoeJoe
    6LarryLarry
    7BobbyMary
    8DorisBobby
    9JaneDoris
    10JoeJane
    11LarryJoe
    12BobbyLarry
    13DorisMary
    14JaneBobby
    15JoeDoris
    16LarryJane
    17BobbyJoe
    18DorisLarry
    19JaneMary
    20JoeBobby
    21LarryDoris
    22BobbyJane
    23DorisJoe
    24JaneLarry
    25JoeMary
    26LarryBobby
    27BobbyDoris
    28DorisJane
    29JaneJoe
    30JoeLarry
    31LarryMary
    32 Bobby
    33 Doris
    34 Jane
    35 Joe
    36 Larry
    37 Mary
    38
    39

    Spreadsheet Formulas
    CellFormula
    B2=IF(ROWS(B$2:B2)>ROWS(A$2:A$31)+COUNTIF(A$2:A$31,A$2),"",IF(B1="Larry","Mary",IF(B1="Mary",A$2,INDEX(A$1:A$31,MATCH(B1,A$1:A$31,0)+1))))


    Excel tables to the web >> Excel Jeanie HTML 4



    Now here is another manual way in case that is of interest
    1. Put AutoFilter on column A and filter for the first name in the list (Bobby in this case
    2. Now click and drag down to select from the second Bobby to and including the first blank cell below the last Bobby
    3. Press F5 -> Special... -> Visible cells only -> OK
    4. Without changing the selection, use the AutoFilter drop-down again to 'Select All'
    5. On the Home ribbon tab choose 'Insert' drop-down in the 'Cells' group and choose Insert Sheet rows
    6. Now select the whole of column A by clicking its heading label
    7. F5 -> Special... -> Blanks -> OK
    8. Type Mary and confirm with Ctrl+Enter, not just Enter

    While I'm at it, here is another macro that you could consider.
    This code ..
    - Assumes the list starts in A1 (that is no heading). If there is a heading, change the two "A1" references in the code to "A2" (no need to change the "A2" that is already there)
    - Doesn't require the hard-coding of the last name (Larry) as it just puts Mary before the list starts repeating, whatever that last name is.

    Code:
    Sub Insert_Mary()
      With Range("A1", Columns("A").Find(What:=Range("A1").Value, After:=Range("A2"), LookAt:=xlWhole))
        .Cells(.Cells.Count).Value = "Mary"
        .Copy .Resize(.Rows.Count * Application.CountIf(.CurrentRegion, .Cells(2).Value))
      End With
    End Sub
    A further comment is that you actually asked about "inserting a row" for Mary and the only suggestions in the thread up to here to actually do that (probably important if there is other data in the sheet that needs to stay with its own row) are JoeMo's and the 'maual' one i outlined above. If actual row insertion is required, here is another macro to consider.
    Code:
    Sub Insert_Mary_v2()
      Dim r As Long
      Dim LastName As String
      
      LastName = Range("A" & Rows.Count).End(xlUp).Value
      Application.ScreenUpdating = False
      For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("A" & r).Value = LastName Then
          Rows(r + 1).Insert
          Range("A" & r + 1).Value = "Mary"
        End If
      Next r
      Application.ScreenUpdating = True
    End Sub
    Last edited by Peter_SSs; Sep 22nd, 2019 at 01:31 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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