Posted on 2011-10-11
Last Modified: 2012-05-12
Hi Guys

I am trying to allocate Names in the column by the amount of blank space and the number of time it should appear. Attached the example excel.Any help will be appreciated.

Question by:surah79
    LVL 20

    Expert Comment

    by:Hendrik Wiese
    For the Times you would use the following in C2:


    Open in new window

    For the Blank Spaces from the first X to the last X you would use the following:


    Open in new window

    I have attached the edited version for you reference.


    Author Comment


    Thanks for reply, I am sorry that I was not clear enough, I don't want to count, what I want is c opy
    if X  4 times in the column with 1 blank row(depends on howmany blank row I would put the number in it) in between, or copy Y in the same column after A 2 times and
    Name  Times blankSpace
    X            2             1            so it X should be copied in the column 2 times and 1 row should be blank in between them so the output after the macro will be look as
    LVL 20

    Expert Comment

    by:Hendrik Wiese
    No sure what you mean..

    Please edit the excel sheet with descriptions next to the shells explaining what you want them to do and then upload it again?


    Author Comment

    Hi attached the new excel
    LVL 17

    Accepted Solution

    Try this - you need to select the range containing your instructions (without the headers), then click on the button - this will call this macro code:
    Public Sub InsertNames(ByRef rngIn As Excel.Range)
        Dim rngOut As Excel.Range
        Dim c As Excel.Range
        Dim i As Long, j As Long, r As Long
        If rngIn.Columns.Count < 3 Then
            MsgBox "Please select a range containing at least 3 columns " & _
                "(Name, times, blank spaces)"
            Exit Sub
        End If
        ' this places the output below the input range, starting in the same column
        ' as the Name col - you could change this to be on a different sheet
        ' if you like
        Set rngOut = rngIn.Worksheet.UsedRange
        Set rngOut = rngOut.Offset(rngOut.Rows.Count, rngIn.Column - rngOut.Column).Resize(1, 1)
        For Each c In rngIn.Columns(1).Cells
            ' get the number of blank lines
            r = c.Offset(0, 2).Value
            ' loop as many times as requested
            For i = 1 To c.Offset(0, 1).Value
                rngOut.Cells(1, 1).Value = c.Value
                ' add the blank lines
                For j = 1 To r
                    Set rngOut = rngOut.Offset(1, 0)
                Next j
                'move to the next cell
                Set rngOut = rngOut.Offset(1, 0)
            Next i
        Next c
    End Sub

    Open in new window


    Author Closing Comment

    Awesome thanks a lot

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now