Announcement

Collapse
No announcement yet.

VBA Code To Prompt User To Insert X Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code To Prompt User To Insert X Rows



    Can any one tell me what is wrong with this code please?

    I am trying to insert rows from an InputBox in several sections of a sheet then copy the formulas down into the inserted cells in each section.

    each row in each section = section one rows

    Code:
    Public rng As Long
    
    Sub changesheetsize()
            
            Dim AC_7 As Range
            Dim AC_6 As Range
            Dim AC_5 As Range
            Dim AC_4 As Range
            Dim AC_3 As Range
            Dim AC_2 As Range
            Dim AC_1 As Range
            
            Set AC_7 = Range("A65536").End(xlUp)
            Set AC_6 = AC_7.End(xlUp)
            Set AC_5 = AC_6.End(xlUp)
            Set AC_4 = AC_5.End(xlUp)
            Set AC_3 = AC_4.End(xlUp)
            Set AC_2 = AC_3.End(xlUp)
            Set AC_1 = AC_2.End(xlUp)
            Application.ScreenUpdating = False
        
            rng = InputBox("Enter number of rows required.")
                    AC_1.Select
                    Set AC_1 = AC_1.Offset(-rng, 0)
                    Call InsertRow
                    AC_2.Select
                    Set AC_2 = AC_2.Offset(-rng, 0)
                    Call InsertRow
                    AC_3.Select
                    Set AC_3 = AC_3.Offset(-rng, 0)
                    Call InsertRow
                    AC_4.Select
                    Set AC_4 = AC_4.Offset(-rng, 0)
                    Call InsertRow
                    AC_5.Select
                    Set AC_5 = AC_5.Offset(-rng, 0)
                    Call InsertRow
                    AC_6.Select
                    Set AC_6 = AC_6.Offset(-rng, 0)
                    Call InsertRow
                    AC_7.Select
                    Set AC_7 = AC_7.Offset(-rng, 0)
                    Call InsertRow
          
                    AC_7.Select
                    Call fdownformula
                    AC_6.Select
                    Call fdownformula
                    AC_5.Select
                    Call fdownformula
                    AC_4.Select
                    Call fdownformula
                    AC_3.Select
                    Call fdownformula
                    AC_2.Select
                    Call fdownformula
                    AC_1.Select
                    Call fdownformula
                    
            Application.ScreenUpdating = True
            
    End Sub
    
    Sub InsertRow()
        
        If rng = 0 Then Exit Sub
        Range(ActiveCell, ActiveCell.Offset(Val(rng) - 1, 0)).EntireRow.Insert
    End Sub
    
    Sub fdownformula()
        Dim n As Long, k As Long
        'need To know how many formulas To copy down.
        'Assumes from A over To last entry In row.
        k = ActiveCell.Offset(-1, 0).Row
        n = Cells(k, 256).End(xlToLeft).Column
        Range(Cells(k, 1), Cells(k + Val(rng), n)).FillDown
    End Sub
    Attached Files

  • #2
    Re: insert rows, InputBox, filldown

    First reaction, start from AC7, instead of AC1
    Cheers,

    S M C

    Click To Read: How To Use Tags In Your Threads/Posts
    Please take time to read Forum Rules before posting
    Message To Cross Posters

    Comment


    • #3
      Re: insert rows, InputBox, filldown

      Now getting run error 1004
      aplication/object defined error
      At

      Code:
       Set AC_1 = AC_1.Offset(-rng, 0)
      Last edited by LucyLoo; March 3rd, 2012, 00:33. Reason: remove picture address

      Comment


      • #4
        Re: insert rows, InputBox, filldown

        Try this..

        Code:
        Sub changesheetsize()
        
            Dim rng As Range
            Dim lngRows As Long
            
            lngRows = InputBox("Enter number of rows required.")
            For Each rng In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants, 1)
                rng.Resize(lngRows).EntireRow.Insert
                rng.Offset(-lngRows - 1).Resize(lngRows + 1, Cells(rng.Row, Columns.Count).End(xlToLeft).Column).FillDown
            Next rng
            
        End Sub
        Cheers,

        S M C

        Click To Read: How To Use Tags In Your Threads/Posts
        Please take time to read Forum Rules before posting
        Message To Cross Posters

        Comment


        • #5
          Re: VBA Code To Prompt User To Insert X Rows

          Code:
          Sub snb()
          y = InputBox("Enter number of rows required.")
          For j = 47 To 5 Step -7
          Rows(j).Resize(y).Insert
          Rows(j).Copy Rows(j).Resize(y)
          Next
          End Sub

          Comment


          • #6
            Re: insert rows, InputBox, filldown

            Thanks smc that works great. sorry for the delay in coming back; been out of th office.

            Comment


            • #7


              Re: insert rows, InputBox, filldown

              Ok I have gone away and tried to learn the special cell method is the below explanation of your code correct. If not would you be so kind to put me in the correct direction please.

              Code:
              Sub changesheetsize()
                  
                  Dim rng As Range
                  Dim lngRows As Long
                  
                  lngRows = InputBox("Enter number of rows required.")
              
                                         Set range colum A,                        rng=Cells that contain Numbers
                  For Each rng In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants, 1) 
              
                      Resize?  by user input ( say user input 10) insert 10 rows
                      rng.Resize(lngRows).EntireRow.Insert
              
                            offset up 9 rows, Resize? (10+1=11rows, need help?
                      rng.Offset(-lngRows - 1).Resize(lngRows + 1, Cells(rng.Row, Columns.Count).End(xlToLeft).Column).FillDown
              
                  Next rng
              would this code run smoother if the rng column A references were at the top left of the inserted cells i.e. the row to be filled down from?
              Last edited by LucyLoo; March 8th, 2012, 01:33. Reason: denote instruction in code

              Comment

              Working...
              X