If you don't want to select or fill 3000 names, try this
ActiveCell.Offset(1, 0).Activate
???
======
"Bradley Dawson" wrote in message
...
If you don't want to select or fill 3000 names, try this:

Sub dptnum()
Dim i%
i = 1
Do While Not (IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0)))
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(0, -1) = i
Else
i = i + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Start at the top of the list with a blank column to the left of the list.
It will stop when two blank rows are encountered.

"Tom" wrote
I have the following scenario
One column in excel contains more than 3000 names
I want to fill the adjacents cells with Department Number
Fortunately all the names belonging to same department are
coming together followed by an EMPTY CELL Then the second
Department names start followed by an EMPTY CELL then
third Department names being and so on "all these under
one column"
I need to tell Excel to start filling the adjacent cell of
Dept1 names with Number 1 and continue down stream
Till it found the adjacent cell is EMPTY (in our example
the cell between Tom and James) Stop filling and Jump to
next cell and start filling with number 2 till it found
the second Empty cell stop
filling and Jump to adjacent cell and start
filling with number 3 and so on....

Your solution is great! I'm just saying that with a large list that
contains blank rows, selecting it can be almost as tedious as a fill.
Especially with some of the lists I deal with. (26K to 30K rows). So in
this code either a department number is placed next to the list or the
department number is incremented depending on if the entry is blank or not.
Then the activecell moves down one. When it gets to two blank rows it
stops. I suppose going back to the top of the list after it completes would
have been a nice touch, though.

Anyway, it worked fine when I tested it.

Questions?

"Don Guillett" wrote in message
...
If you don't want to select or fill 3000 names, try this
ActiveCell.Offset(1, 0).Activate
???
======
"Bradley Dawson" wrote in message
...
If you don't want to select or fill 3000 names, try this:

Sub dptnum()
Dim i%
i = 1
Do While Not (IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0)))
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(0, -1) = i
Else
i = i + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

If you like it that way, I love it.
I guess that activate and select are two different things.

"Bradley Dawson" wrote in message
...
Your solution is great! I'm just saying that with a large list that
contains blank rows, selecting it can be almost as tedious as a fill.
Especially with some of the lists I deal with. (26K to 30K rows). So in
this code either a department number is placed next to the list or the
department number is incremented depending on if the entry is blank or
not.
Then the activecell moves down one. When it gets to two blank rows it
stops. I suppose going back to the top of the list after it completes
would
have been a nice touch, though.

Anyway, it worked fine when I tested it.

Questions?

"Don Guillett" wrote in message
...
If you don't want to select or fill 3000 names, try this
ActiveCell.Offset(1, 0).Activate
???
======
"Bradley Dawson" wrote in message
...
If you don't want to select or fill 3000 names, try this:

Sub dptnum()
Dim i%
i = 1
Do While Not (IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1,
0)))
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(0, -1) = i
Else
i = i + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

Activate and Select are very similar. As far as moving the activecell,
they're the same.

"Don Guillett" wrote in message
...
If you like it that way, I love it.
I guess that activate and select are two different things.

You said "If you don't want to SELECT."
then you should ACTIVATE????

"Bradley Dawson" wrote in message
...
Activate and Select are very similar. As far as moving the activecell,
they're the same.

"Don Guillett" wrote in message
...
If you like it that way, I love it.
I guess that activate and select are two different things.

It doesn't have to be tedious.

in the name box enter A3:A3000
hit enter
in the formula bar
=IF(B3="","",IF(B2="",A1+1,A2))
Ctrl+enter

done.

Yes, you do have to know what the last line of the data is.

--
Regards,
Tom Ogilvy

Bradley Dawson wrote in message
...
Your solution is great! I'm just saying that with a large list that
contains blank rows, selecting it can be almost as tedious as a fill.
Especially with some of the lists I deal with. (26K to 30K rows). So in
this code either a department number is placed next to the list or the
department number is incremented depending on if the entry is blank or
not.
Then the activecell moves down one. When it gets to two blank rows it
stops. I suppose going back to the top of the list after it completes
would
have been a nice touch, though.

Anyway, it worked fine when I tested it.

Questions?

"Don Guillett" wrote in message
...
If you don't want to select or fill 3000 names, try this
ActiveCell.Offset(1, 0).Activate
???
======
"Bradley Dawson" wrote in message
...
If you don't want to select or fill 3000 names, try this:

Sub dptnum()
Dim i%
i = 1
Do While Not (IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1,
0)))
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(0, -1) = i
Else
i = i + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub