Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Numbering column A

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On my spreadsheet Column B has a series of data and every so often there is a blank row (to separate the data). I would like to use column A to place a number next to each data row in column B but, do not want the blank rows to be numbered. I tried to use "FILL" but, it numbered the blank rows.

    Please advise.

    Thx,
    Noir

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How big do the gaps get? One cell? Two cells? More?

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 08:27, Noir wrote:
    On my spreadsheet Column B has a series of data and every so often there is a blank row (to separate the data). I would like to use column A to place a number next to each data row in column B but, do not want the blank rows to be numbered. I tried to use "FILL" but, it numbered the blank rows.

    Please advise.

    Thx,
    Noir
    In A2 enter and copy down:

    =IF(LEN(B2),MAX($A$1:A1)+1,"")

    where A1 is an empty cell.


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin. Worked perfectly!!

    Noir

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or, if you prefer a VBA approach...

    Sub test()

    Lastrow = Range("B65536").End(xlUp).Row
    Range("A1").Select
    MyRow = ActiveCell.Row

    MyCounter = 1

    While MyRow <= Lastrow

    Range("A" & MyRow).Select
    If ActiveCell.Offset(0, 1).Value <> "" Then
    ActiveCell.Value = MyCounter
    MyCounter = MyCounter + 1
    End If
    MyRow = MyRow + 1

    Wend

    Rgds
    AJ

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AJ how do you initiate your solution?

    Noir

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •