Results 1 to 3 of 3

Thread: Insert 'n' number Rows after any specified row no.

  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14

    Insert 'n' number Rows after any specified row no.

    Hi All,

    I need a VBA Function which takes the "specified row number" and "Number of rows to be inserted" as a parameter.

    Please Help

    Thanx in Advance.

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

    Try this

    Code:
    Sub InsertRows(ByVal RowNo As Long, ByVal NoOfRows As Long, Optional ByVal Sht As Worksheet)
    
        Dim lngSU       As Long
        Dim lngCalc     As Long
    
        With Application
            lngSU = .ScreenUpdating
            .ScreenUpdating = False
            .EnableEvents = False
            lngCalc = .Calculation
            .Calculation = xlCalculationManual
        End With
        
        If Sht Is Nothing Then Set Sht = ActiveSheet
        
        Sht.Rows(RowNo).Resize(NoOfRows).Insert
        
        With Application
            .ScreenUpdating = lngSU
            .EnableEvents = True
            .Calculation = lngCalc
        End With
        
    End Sub
    and call the routine like..

    Code:
    Sub Test()
        InsertRows 10, 5
    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)

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Thanx for this useful function.
    Working!

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2013, 02:22 PM
  2. Replies: 7
    Last Post: 04-21-2013, 07:50 PM
  3. Automatically Insert Row
    By marreco in forum Excel Help
    Replies: 7
    Last Post: 12-21-2012, 06:43 PM
  4. Replies: 2
    Last Post: 10-20-2011, 10:15 AM
  5. Replies: 2
    Last Post: 05-06-2011, 02:59 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
  •