how to insert serial numbers to rows?

[sumesh56]

suppose i have a worksheet in which i have some data in col B with
header on B1
there are blank rows in between data.i want to give serial numbers t
col A irespective of the fact that whether it has data on its right sid
or it is a blank one

i put 1 in col A2. i put =then i click on A2 and then input +1
then i press f5 and put A24 pressed the shift key and pressed enter
then i pressed control D.as a result , i get serial numbers from 1 to 2
in col A

(suppose there are only 10 data rows and others are blanks.
now i want to eliminate the blank rows keeping the serial numbers(in co
A) of the data rows in place

now i enter =COUNTA(B2) in col C.i copy the formula to the last row o
data
i get 1 against data rows and zero against blank rows

now i apply autofilter to col A1 select zero in col C. i get all blan
rows together. i delete them.
now i get the data without the blank rows. the problem i face is that
the serial number in col A has disappeared. it says problem with th
cell reference. now let me ask whether it is possible to do this tas
with the serial numbers in place( of course the numbers will be minu
the blank rows,like 1,2,4,7,12..... i don't mind)

----------------------------
A B
----------------------------
SL.NO TITLE
----------------------------
1 abc
2 wett
3
4 jkhghj
5
6
7 rloljo
8
9
10 fddde
11
12 rderu

 

[Claus Busch]

Hi Sumesh,

Am Fri, 13 Apr 2012 14:52:39 +0000 schrieb sumesh56:

-----------------------------
A B C
-----------------------------
SL.NO TITLE
-----------------------------
1 abc 1
2 wett 1
3 0
4 jkhghj 1
5 0
6 0
7 rloljo 1
8 0
9 0
10 fddde 1
11 0
12 rderu 1

try in B2:
=--SUBTOTAL(3,$B$2:B2)


Regards
Claus Busch

 

[Claus Busch]

Hi Sumesh,

Am Fri, 13 Apr 2012 21:48:03 +0200 schrieb Claus Busch:

try in B2:
=--SUBTOTAL(3,$B$2:B2)

sorry, above is a typo
try in A2:
=--SUBTOTAL(3,$B$2:B2)


Regards
Claus Busch

 

[GS]

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