Automatic numbering in excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dshemesh - 24 May 2006 11:43 GMT

Hello, I am very novice in excel, and have encountered the following problem:
I have a file with a few tables (I use page breaks between 2 tables). The
first column of each table is a serial number column. So I want this column
to automatically start from number 1 (not including the first row, which is a
title row), and keep going, until a page break is encountered (but not when a
regular page ends). How can I do this? I want it of course to handle new
lines as well (every line that I add or remove from a table should change all
serial numbers as needed).

thanks,

Signature

dshemesh

Reply to this Message

Ed Ferrero - 24 May 2006 14:53 GMT

Hi dshemesh,

You colud do this with a macro.

Copy the code below (between the ====) to the Visual Basic Editor in Excel.

(In Excel press Alt-F11 to open the VBE, then Insert>Module)

Then select the cell where you want the serial numbers to start, and run the
macro.

'=========================================================================
Sub SetIncrements()
Dim r As Range
Dim i, j As Integer

Set r = Selection

If r.Cells.Count > 1 Then
MsgBox "Select one cell only"
Exit Sub
End If

j = 0

For i = 0 To ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Rows.Count,
1).Row - r.Row
j = j + 1
If r.Offset(i, 0).EntireRow.PageBreak = xlPageBreakManual Then j = 1
r.Offset(i, 0).Value = j
Next i

End Sub
'=========================================================================

Ed Ferrero
http://www.edferrero.com

> Hello, I am very novice in excel, and have encountered the following
> problem:
[quoted text clipped - 11 lines]
>
> thanks,

Reply to this Message