Quote:
Originally Posted by GS[_2_]
Here's one method I use to auto-number entries in a table. Removing
rows will cause the remaining rows to automatically renumber, adjusting
for rows that are added/removed...
Select cell A2
Open the Define Names dialog;
In the Name box type: '<sheetname'!LastCell
..where you substitute the actual sheetname for <sheetname;
In the RefersTo box enter =A1
Click the OK button to close the dialog
In cell A2 enter 1
In all the cells below enter =LastCell+1
If you want the cells to display a format then use Custom on the Number
Format tab of the Format Cells dialog.
Another way:
If you don't want to use a defined name then you can use the Row()
function as follows...
Scenario is Row1 has headers, data starts in Row2
In A2 type =Row()-1
Optionally, you just need to minus the row number immediately above the
first row of data if the start row isn't Row2. So.., if data starts in
Row6 then the formula to auto-number the data is...
=Row()-5
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public. vb.general.discussion |
dear Garry,
it is perfect. it works.this is what i was searching for.thanks a lot and have a nice day.
i would like to give a description of my experience to those who want it.
1.suppose you have data in col A.there are blank rows in between data rows. you want to delete the blank rows and to get the data rows serial numbered.
2.give a name to the sheet(eg=test)
3.insert a col on the left. now the data in col B.
4.you have a header in B1.data from B2
5.select col A2.enter =Row()-1
in col A3 enter =A2+1
home-find-goto A50 pressing the shift p enter. then press control D. now you have serial numbers in col A from 1 to 50.
6.now home-find-goto-enter-A50:B50
pressing the shift p enter.find-goto special-blanks-p enter
7.then you press the delete btn on the tool bar. now the blank rows have been deleted and you get the data rows fully numbered.
-----------------------