Insert Serial Number

[adam]

Hi anyone,

I'm using the following code to insert serial numbers to column B in reference to column C.

My data starts from row 9. The code works fine if there's data in column C. But if there's no data in column C the code inserts serial numbers until the last row of the worksheet. How could I avoid that?

I want the code also to insert lines to the data rows from C9:G9 to last data row.

Code: Select all

Sub Serial()
    Dim rLastCell   As Range
    Dim i           As Long
    
    Application.ScreenUpdating = False
    
    With Worksheets("sheet1")
        Set rLastCell = .Range("C9").End(xlDown)
        .Range("B9").Value = 1
        .Range(.Range("B9"), rLastCell.Offset(, -1)).DataSeries _
                             step:=1, stop:=rLastCell.Row - 1
                For i = 7 To 12
            .Range(.Range("C9"), _
                rLastCell.Offset(, 1)).Borders(i).LineStyle = xlContinuous
        Next i             
                             
    End With
    Application.ScreenUpdating = True
End Sub

Any help would be kindly appreciated.

[HansV]

Simplified code:

Code: Select all

Sub Serial()
    Dim r As Long

    Application.ScreenUpdating = False
    With Worksheets("sheet1")
        r = .Range("C" & .Rows.Count).End(xlUp).Row
        If r >= 9 Then
            .Range("B9").Value = 1
            .Range("B9:B" & r).DataSeries
            .Range("C9:G" & r).Borders.LineStyle = xlContinuous
        End If
    End With
    Application.ScreenUpdating = True
End Sub

[adam]

Thankyou Hans. It worked really well.