Autofill Problem

eddiesvoicebox · #1 (**permalink**) 07-10-09, 09:23

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

texasalynn · #2 (**permalink**) 07-10-09, 09:43

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

eddiesvoicebox · #3 (**permalink**) 07-10-09, 09:55

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

Internet Services

Web Development

Digital Marketing

Consumer Tech