Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbering cells
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbering cells
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 -- Gary''s Student - gsnu200909 "Soth" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbering cells
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] On Fri, 4 Dec 2009 08:58:01 -0800, Soth wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbering cells
You're the best. Thanks
"Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200909 "Soth" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Numbering a merged Cells Excel 2003 | Excel Discussion (Misc queries) | |||
Numbering cells that have been filtered | Excel Worksheet Functions | |||
set up auto numbering of cells | Excel Discussion (Misc queries) | |||
automatic numbering of cells in excel | Excel Discussion (Misc queries) | |||
sequential numbering in non adjacent cells | Excel Worksheet Functions |