Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 122
Default incrementing every two rows

Hello:

I have a spreadsheet, and I need to number each set of two rows throughout
the spreadsheet.

For example, the first two rows need to be numbered 1, the second two rows
need to be numbered 2, the third two rows need to be numbered 3, and so forth.

This spreadsheet has over 16,000 rows. Is there a quick way of doing this?

Thanks!

childofthe1980s
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default incrementing every two rows

One way:

Select A1:A16xxx. Enter this in the formula bar using CTRL-Enter

=INT((ROW()+1)/2)

In article ,
childofthe1980s wrote:

Hello:

I have a spreadsheet, and I need to number each set of two rows throughout
the spreadsheet.

For example, the first two rows need to be numbered 1, the second two rows
need to be numbered 2, the third two rows need to be numbered 3, and so forth.

This spreadsheet has over 16,000 rows. Is there a quick way of doing this?

Thanks!

childofthe1980s

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default incrementing every two rows


Good evening Childofthe1980s

You can do this through using VBA. Copy this code to the clipboard:

Sub NumberRow()
Application.ScreenUpdating = False
a = 1
For b = 1 To 8000
ActiveCell.Value = a
ActiveCell.Offset(rowOffset:=1).Activate
ActiveCell.Value = a
ActiveCell.Offset(rowOffset:=1).Activate
a = a + 1
Next b
Application.ScreenUpdating = True
End Sub

In excel right click your sheet tab and select View Code and paste thi
into the blank window. The "For B=..." line, make the 8000 exactly hal
of the number of cells your are numbering, then close the window and g
back to Excel. Click on the first cell you want to number and go t
Tools Macro Macros, select "NumberRow" and click OK.

The routine may take a second or so to run depending on the speed o
your system.

HTH

Dominic

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...fo&userid=1893
View this thread: http://www.excelforum.com/showthread.php?threadid=38212

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 303
Default incrementing every two rows

Interesting solution from JE again........
Keep forgetting about the CNTRL-ENTER

Of course you would after that do the <edit<copy<paste<paste
special<values thing to convert the
formula's to values.

One other way would be to extend a simple 1,2,3 range to 8xxx (i.e. halfway)
copy that and paste it into the second half and finish with a sort.
You may have to do this on a empty sheet and copy the result across.

--
Greetings from New Zealand
Bill K

"JE McGimpsey" wrote in message
...
One way:

Select A1:A16xxx. Enter this in the formula bar using CTRL-Enter

=INT((ROW()+1)/2)

In article ,
childofthe1980s wrote:

Hello:

I have a spreadsheet, and I need to number each set of two rows
throughout
the spreadsheet.

For example, the first two rows need to be numbered 1, the second two
rows
need to be numbered 2, the third two rows need to be numbered 3, and so
forth.

This spreadsheet has over 16,000 rows. Is there a quick way of doing
this?

Thanks!

childofthe1980s



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incrementing Rows and Columns Intern Excel Discussion (Misc queries) 1 June 5th 09 05:59 PM
fill down formula incrementing by every 12 rows westy Excel Discussion (Misc queries) 2 September 5th 07 04:54 AM
Incrementing formula by two rows Ben Hur Excel Worksheet Functions 1 February 24th 05 09:21 PM
Automatically 'incrementing' formulas for new rows. MediaScribe New Users to Excel 3 February 21st 05 06:29 PM
Problem incrementing rows hotherps[_12_] Excel Programming 10 February 18th 04 07:00 PM


All times are GMT +1. The time now is 04:11 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"