Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Autofill Problem

    Good afternoon everyone,

    Hopefully an easy one for you on this sunny friday afternoon (well it is in london anyway).

    I have a workbook that is filled out by users. As a result of them keep inserting rows and knocking my formulas out of whack i have added an insert row button for them. The problem i have is that i have a rownumber counter in column A. when they insert a new row i want the column to autofill from 1 down to the last row. Currently the code is set to go down 150 rows but as i add each new row it still only goes down to 150 rows.

    I hope that makes sense, here is the code im currently running for this procedure. The problem i have is of course in the last row.

    Code:
    Sub Insert_Row()
    
    'messagebox asking you to select the row number where you would like to insert the row
    'adds 39 to the number entered to allow for hidden rows
        rownum = InputBox("Please Enter The Row Number You Would Like To Add A New Row To") + 39
            
            If rownum < 41 Then
                MsgBox ("You Cannot Insert A Row Here Please Try Another Row")
                GoTo hell:
            End If
    'inserts the row and shifts everything down 1 row
            Rows(rownum).Select
                Selection.Insert Shift:=xlDown
    
    'autofills the row count at the bottom so that shows 1-150 with no interruptions
            Range("A40").Select
                Selection.AutoFill Destination:=Range("A40:A189"), Type:=xlFillSeries
    hell:
    
    End Sub
    Any help you can provide would be most helpful.

    Thanks

    Chris

  2. #2
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    add a variable

    Code:
    Dim LR As Long
    LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Range("A40").AutoFill Destination:=Range("A40:A" & LR), Type:=xlFillSeries
    texasalynn
    It's AWL Good!

  3. #3
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Hi Lynn,

    Thanks for the response. Had a little trouble with that code as the variable kept coming back with row 39 (not 190 or so) however you made me think logically about the problem and i realised the easiest way to do it was your way.

    For reference the line i changed was:

    Code:
    LR = Range("a65000").End(xlUp).Row
    All sorted, thanks for your help

    Chris

Posting Permissions

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