Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Insert blank rows based on cell value

  1. #1
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0

    Insert blank rows based on cell value

    hello vba experts...

    i have code for "insert blank rows between base on count of each rows" but does'nt perfectly work, see this below :

    Code:
    Sub muhammad_susanto()
    'insert blank rows between base on count of each rows'
    Dim LR As Long
    Dim I As Long
    Dim Ofst As Integer
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For I = LR To 1 Step -1
        Ofst = Cells(I, 1).Value
        Ofst = Ofst - 1
        If Ofst = 0 Then
            Else
            Cells(I + 1, 1).Resize(Ofst).EntireRow.Insert
            Cells(I, 2).Resize(Ofst + 1).Value = Cells(I, 2).Value
        End If
    Next I
    End Sub
    it's error in the line :
    Code:
    Cells(I + 1, 1).Resize(Ofst).EntireRow.Insert
    i appreaciated who want's help me...

    regards...
    muhammad susanto

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    your code works on my test sheet, attach a sample file with data and desired result

  3. #3
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    ok. if i execute at first code it's work, but more than/once again it's error..
    Attached Files Attached Files

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try

    Code:
    Sub muhammad_susanto()
        'insert blank rows between base on count of each rows'
        Dim LR As Long
        Dim I As Long
        Dim Rng As Range
        
        Dim Ofst As Integer
        LR = Cells(Rows.Count, "C").End(xlUp).Row
        
        Set Rng = Range("c16:c" & LR)
        With Rng
            For I = .Rows.Count To 1 Step -1
                Ofst = .Cells(I, 1).Value
                If Ofst > 0 Then
                    Ofst = IIf(Ofst = 1, 1, Ofst - 1)
                    .Cells(I + 1, 1).Resize(Ofst).EntireRow.Insert
                    .Cells(I, 2).Resize(Ofst + 1).Value = .Cells(I, 2).Value
                End If
            Next I
        End With
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    or
    Code:
    Sub M_snb()
        sn = Cells(13, 3).CurrentRegion.Offset(3)
        sp = Cells(13, 3).CurrentRegion.Offset(3).Resize(Application.Sum(Application.Index(sn, 0, 1)) + UBound(sn))
        
        For j = 1 To UBound(sn)
          c00 = c00 & "," & j & Replace(String(sn(j, 1), ","), ",", "," & UBound(sp))
        Next
        
        Cells(13, 3).CurrentRegion.Offset(3).ClearContents
        Cells(16, 3).Resize(UBound(sp), UBound(sp, 2)) = Application.Index(sp, Application.Transpose(Split(Mid(c00, 2), ",")), [transpose(row(1:10))])
    End Sub

  6. #6
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    Thanks you, admin...it's realy work...

  7. #7
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    sorry, guys..i am a mistake..my code that's i want with criteria like this :

    - base upon count of of each rows with assuming, if count of rows = 0 or 1, nothing inserted row;
    - if count of rows >= 2, inserted row minus 1, for example :
    count of row = 2.....result inserted row = 1
    count of row = 3.....result inserted row = 2
    count of row = 5.....result inserted = 4
    etc....

    i hope somebody would help me.

    regards...
    m.susanto

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    You can easily adapt the suggestions you received yourself.

  9. #9
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by muhammad susanto View Post
    sorry, guys..i am a mistake..my code that's i want with criteria like this :

    - base upon count of of each rows with assuming, if count of rows = 0 or 1, nothing inserted row;
    - if count of rows >= 2, inserted row minus 1, for example :
    count of row = 2.....result inserted row = 1
    count of row = 3.....result inserted row = 2
    count of row = 5.....result inserted = 4
    etc....

    i hope somebody would help me.

    regards...
    m.susanto
    That's what the code does, isn't it ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  10. #10
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    your's code it's work normaly base upon count of each row,
    i suggest that's code base upon with keep if amount of row = 0 or 1, nothing inserted rows, otherwise inserted rows minus 1 if amount of row greater than or equal to 2

    1 -----title1
    2 -----title2
    ------title2
    3------title3
    ------title3
    ------title3
    1------title4

Similar Threads

  1. Replies: 5
    Last Post: 07-11-2013, 07:31 AM
  2. Insert Picture in a Cell UDF
    By Admin in forum Download Center
    Replies: 10
    Last Post: 12-07-2012, 04:49 PM
  3. Autofill the data based on non blank cell in next row?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 3
    Last Post: 11-29-2012, 04:16 PM
  4. Insert 'n' number Rows after any specified row no.
    By LalitPandey87 in forum Excel Help
    Replies: 2
    Last Post: 11-08-2011, 08:59 AM
  5. Deleting blank rows
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-14-2011, 03:14 AM

Posting Permissions

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