# Sequential numbering with varying spaces

• ### Question

• Hi All....

I have columns C and D, with Names down column D.  there are varying numbers of blank rows between the names. I need a macro to start in C13 and put a sequential number down column C for each name in column D, no matter how many spaces between the names in column D.

Vaya con Dios,

Chuck, CABGx3

Chuck, CABGx3
Thursday, June 23, 2011 11:14 PM

• hi  Chuck,

Dim n As Integer, i As Integer
n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
For i = Range("D65536").End(xlUp).Row To 13 Step -1
If Range("D" & i) <> "" Then Range("C" & i) = n:  n = n - 1
Next

hola Bélgica

--
isabelle

• Marked as answer by Friday, June 24, 2011 1:45 AM
Friday, June 24, 2011 12:01 AM
• hi  Chuck,

i'm glad i was able to help,
for more caution, perhaps you should check that the cell value is text
by adding an other condition "Not IsNumeric"

Dim n As Integer, i As Integer
n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
For i = Range("D65536").End(xlUp).Row To 13 Step -1
If Not IsNumeric(Range("D" & i)) And Range("D" & i) <> "" Then Range("C" & i) = n: n = n - 1
Next

--
isabelle

• Marked as answer by Friday, June 24, 2011 1:45 AM
Friday, June 24, 2011 1:44 AM

### All replies

• hi  Chuck,

Dim n As Integer, i As Integer
n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
For i = Range("D65536").End(xlUp).Row To 13 Step -1
If Range("D" & i) <> "" Then Range("C" & i) = n:  n = n - 1
Next

hola Bélgica

--
isabelle

• Marked as answer by Friday, June 24, 2011 1:45 AM
Friday, June 24, 2011 12:01 AM
• Absolutely OUTSTANDING!!!

Your code fills my bill perfectly!

You are most kind, and so very, very talented.

Many thanks,

Vaya con Dios,

Chuck, CABGx3

Chuck, CABGx3
Friday, June 24, 2011 12:16 AM
• hi  Chuck,

i'm glad i was able to help,
for more caution, perhaps you should check that the cell value is text
by adding an other condition "Not IsNumeric"

Dim n As Integer, i As Integer
n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
For i = Range("D65536").End(xlUp).Row To 13 Step -1
If Not IsNumeric(Range("D" & i)) And Range("D" & i) <> "" Then Range("C" & i) = n: n = n - 1
Next

--
isabelle

• Marked as answer by Friday, June 24, 2011 1:45 AM
Friday, June 24, 2011 1:44 AM
• Hi isabelle

n = 1000 + Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)") 'Starts with #1001 in C13

Other than that, it worked perfectly.  I'll check your new addition out tomorrow as it's my bedtime now here in St. Petersburg, Florida.

Many thanks again

Vaya con Dios,

Chuck, CABGx3

Chuck, CABGx3
Friday, June 24, 2011 1:49 AM
• please my mistake, i said "hola Bélgica" because i have thought of music band "Vaya con Dios" from belgium
so good night Petersburg, Florida

--