ActiveCell.EntireRow Fill Series 1

[HomeGrowth]

Hi There, I need some expertise

I want to add a row to a protected worksheet. The sub below does it OK, except I need to "fill series of the first column" instead just copy. My first column is just a sequencial number, now have 10 rows, and end with 10. The first add should give me "11", next add give me "12" and so on. So, how do I modify my ActiveCell.EntireRow.FillDown code to do just that. Thank you.

Sub AddRowForMe()

'Unprotect the workshet

ActiveSheet.Unprotect

'Start at cell A11, loop to the next empty cell,

Range("A11").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'then copy fill down.

ActiveCell.EntireRow.FillDown

'Reprotect the sheet
ActiveSheet.Protect

End Sub

 

[PHV]

Something like this ?
Sub AddRowForMe()
ActiveSheet.Unprotect
Dim lngRow As Long
lngRow = Range("A:A").Find("*", , , , xlByRows, xlPrevious).Row
Cells(lngRow + 1, 1) = Cells(lngRow, 1) + 1
ActiveSheet.Protect
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HomeGrowth]

PHV, thanks for the suggested codes. It does fill the next cell with increment number, but

ActiveCell.EntireRow.FillDown

helps filling the rest of the cells in that row with formulas from the previous row; how can I accomplish both. I tried to combine both, but it filled another row. Maybe I need someone interpret the code so I can modify it. Thanks

 

[PHV]

Perhaps this ?
Something like this ?
Sub AddRowForMe()
ActiveSheet.Unprotect
Dim lngRow As Long
lngRow = Range("A:A").Find("*", , , , xlByRows, xlPrevious).Row
Cells(lngRow + 1, 1).EntireRow.FillDown
Cells(lngRow + 1, 1) = Cells(lngRow, 1) + 1
ActiveSheet.Protect
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HomeGrowth]

Beautifully done. you got a star from me. Thank you.