Results 1 to 8 of 8

insert blank row between list of numbers and names

This is a discussion on insert blank row between list of numbers and names within the Excel Questions forums, part of the Question Forums category; Column A contains numbers in chronological order followed by names in alphabetical order. I need VBA code to insert a ...

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    28

    Default insert blank row between list of numbers and names

    Column A contains numbers in chronological order followed by names in alphabetical order. I need VBA code to insert a blank row between the last number and the first name. Thanks.

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,247

    Default Re: insert blank row between list of numbers and names

    So is it enough to say the blank row should just be inserted when the number in Col A changes?

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    8,376

    Default Re: insert blank row between list of numbers and names

    Maybe this
    Code:
    Sub test()
    Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
        For r = lr To 2 Step -1
            If Application.WorksheetFunction.IsText(Range("A" & r)) And Application.WorksheetFunction.IsNumber(Range("A" & r - 1)) Then
               Rows(r).EntireRow.Insert
            End If
         Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    28

    Default Re: insert blank row between list of numbers and names

    No. A single blank row must be inserted between the last number in the column and the first name. To explain: This is a data validation list containing mostly ID numbers, but some names of those who don't have ID numbers. I want the data entry people to enter a 5 or 6 digit number to populate the columns to the right. If there is no number available, they will use the drop down list (validation list) to click on the name. It's easier to enter the number than to scroll through a list of names. The blank row will open the validation list at that point, skipping over all the 200 or so numbers above it.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    28

    Default Re: insert blank row between list of numbers and names

    Phooey! I wrote my reply before scrolling down. The code worked perfectly! Thanks so much! This was the last piece of an involved set of macros that I've written/recorded/taken from Mr. Excel and others. I'm a novice, but I'm learning. You people have been such a big help. Thanks again!!!

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    8,376

    Default Re: insert blank row between list of numbers and names

    Glad it was what you needed..
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,247

    Default Re: insert blank row between list of numbers and names

    The code worked perfectly!
    Well done Michael

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    8,376

    Default Re: insert blank row between list of numbers and names

    Hey Robert, thanks....I was a bit like you though, taking a stab....
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

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
  •  


DMCA.com