numbering cells

[Soth]

Hi-

I have over 200,000 lines and I'd like to numbering each cell 123...etc.
The problem is some cells are blank and I can't seem to do as much auto
refill and it just time consuming to drag cell all the way to 200K line. So
i'm wondering if there is a quickest way to numbering cell from 1 through
200000

Thanks
Soth

 

[Gary''s Student]

Running this tiny macro will number rows 1 thro 1000 in column A:

Sub NumberThem()
Set r = Range("A1:A1000")
r.Formula = "=row()"
End Sub

Make the obvious adjustments to change the column or the last row.

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Chip Pearson]

Code like the following will number the rows, skipping blanks. In the
code, change

Const COL As String = "A"
to the column letter in which the number should be placed.

Const TEST As String = "B"
to the column letter of the cell that if blank indicates that the row
is not to be numbered.

Const START_ROW = 1
to the first row of data to test.

Set WS = Worksheets("Sheet1")
to the name of the appropriate worksheet


Sub NumberCells()
Const COL As String = "A"
Const TEST As String = "B"
Const START_ROW = 1

Dim N As Long
Dim L As Long
Dim LastRow As Long
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
With WS
LastRow = .Cells(.Rows.Count, TEST).End(xlUp).Row
End With
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
With WS
For N = START_ROW To LastRow
If Len(.Cells(N, TEST).Value) > 0 Then
L = L + 1
.Cells(N, COL).Value = L
End If
Next N
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Soth]

You're the best. Thanks

Running this tiny macro will number rows 1 thro 1000 in column A:

Sub NumberThem()
Set r = Range("A1:A1000")
r.Formula = "=row()"
End Sub

Make the obvious adjustments to change the column or the last row.

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm