Solved: Auto Numbering Requirement

[rajkumar]

Hi,

I have tried Auto Numbering code in the knowledge base, it is amazing!

'Put this code In the sheet module:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim RowOffset As Long
Dim IndexCol As String
'Set values
RowOffset = 0
'Change the C to the column where you want the numbers to show
IndexCol = "C"

Intersect(ActiveCell.EntireRow, Columns(IndexCol)).Value = ActiveCell.Row + RowOffset
End Sub

But it starts from cell A1, where as my data starts from 7th Row.

Can any body help me how to change this to work from Row no 7.

Thanks in advance
Raj

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim RowOffset As Long
Dim IndexCol As String
'Set values
RowOffset = 0
'Change the C to the column where you want the numbers to show
IndexCol = "C"

If Target.Row >= 7 Then
Intersect(Target.EntireRow, Columns(IndexCol)).Value = Target.Row + RowOffset
End If
End Sub

[rajkumar]

The code works well,but it inputs the auto number also 7 in the seventh row

I need auto number to start with no 1.

Thanks
Raj

[shamsam1]

try this

Dim RowOffset As Long
Dim IndexCol As String
'Set values
RowOffset = 0
'Change the C to the column where you want the numbers to show
IndexCol = "C"

If Target.Row >= 7 Then
Intersect(Target.EntireRow, Columns(IndexCol)).Value = Target.Row - 6 + RowOffset
End If

[rajkumar]

Great ! Cheers
Raj

[rajkumar]

One more query, if i want to stop auto numbering after certain rows, for ex: after 31 rows, how to modify the code.

Raj

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim IndexCol As String
'Set values
'Change the C to the column where you want the numbers to show
IndexCol = "C"

If Target.Row >= 7 And Target.Row <= 31 Then
Intersect(Target.EntireRow, Columns(IndexCol)).Value = Target.Row - 6
End If
End Sub