$10 - Count rows between cells and add rows if needed

  • I have some data in column a which acts as heading, the same text is used for each heading. There could be up to 300 of these headings.


    I would like to be able to count the number of rows from the first instance of this to the next. If its less than 50 rows, add rows until the range has 50 rows.


    So for example, the first instance of this text is in cell A1, the second is in A51, third A72. The number of rows between the first 2 instances is 50 rows so nothing need be done. The number of rows between the second and third instance is only 21 rows so I need to add in 39 rows.


    Can anyone help? Thanks.

  • Re: Count rows between cells and add rows if needed


    Having looked through some other posts on this forum, I found this code that identifies the number of instances of the criteria (in this case the text string start). It also works how how many rows is between each instance of the criteria occuring. It then puts that data into cell B1 of the active sheet.


    This code was created by rbrhodes



    I have tried adapting it to add rows but its way beyond me.

  • Re: $10 - Count rows between cells and add rows if needed


    Hi, I have added a sample workbook and added a text box to describe what I would like the macro to do.

    The sample workbook only shows data in column A, and only a very small sample, which is the only data necessary to achieve my requirements. I state in the workbook that I need it to always be 50 rows between each instance. If possible could this be made into a variable so that I can change the number of rows required between each instance.


    I am unable to use the exact workbook I intend the macro for since it has confidential material on it. I will be transferring the code from this sample workbook to the other workbook.

  • Re: $10 - Count rows between cells and add rows if needed


    Hi


    Have you paid the 10% to OzGrid ?


    Quote

    PLEASE PAY (via PayPal) 10% of your cost to paypal@ozgrid.com BEFORE you post. Then pay the remaining 90% to the person who completes the job on completion


    The code is ready. Please pay the fee (both Oz and to me. See the PM for payment details). Once I received the payment, will post the code here.

  • Re: $10 - Count rows between cells and add rows if needed


    Hi


    Try this


    [vb]Option Explicit


    Sub InsertRows()

    Dim r As Range
    Dim i As Long
    Dim n As Long
    Dim sRow As Long
    Dim l As Long
    Dim SearchKey As String

    Const RowInterval = 50 'Gap between each key word
    Const SearchCol = 1 'search col 'A'
    Const StartRow = 1 'start row

    l = Cells(Rows.Count, SearchCol).End(3).Row 'find last row
    Set r = Cells(StartRow, SearchCol).Resize(l - StartRow + 1)

    SearchKey = LCase(r.Cells(1)) '<<< assume the first cell holds the search string

    Application.ScreenUpdating = False

    For i = r.Rows.Count To 1 Step -1
    If LCase(r.Cells(i, 1)) = SearchKey Then
    If sRow = 0 Then
    sRow = i: n = 1
    ElseIf n < RowInterval Then
    r.Cells(i + 1, 1).Resize(RowInterval - n).EntireRow.Insert
    sRow = i: n = 1
    Else
    sRow = i: n = 1
    End If
    Else
    n = n + 1
    End If
    Next

    Application.ScreenUpdating = True

    End Sub[/vb]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!