count rows down in column from blank to blank

[Danny54]

is there a function that I can insert that will count the # of rows containing data, counting downward until finding a blank row?

im using a vba script that builds the data and I i could insert somekind of function if there is one (perhaps?)


the data looks like this


[TABLE="width: 83"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Article ID[/TD]
[/TR]
[TR]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD]4057115[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD]4057115[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD]4057115[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 83"]
<colgroup><col></colgroup><tbody>[TR]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
When done

[TABLE="width: 83"]
<tbody>[TR]
[TD]Article ID[/TD]
[/TR]
[TR]
[TD]6 [/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD]4057115[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD]4 [/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD]4057115[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD]4057115[/TD]
[/TR]
[TR]
[TD]4474419[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 83"]
<colgroup><col></colgroup><tbody>[TR]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub Danny54()
    Dim Rng As Range
    For Each Rng In Range("A2:A" & Rows.Count).SpecialCells(xlConstants).Areas
        Rng.Offset(-1).Resize(1).Value = Rng.Count
    Next Rng
End Sub

 

[Danny54]

Thanks for the quick response.

What im looking to do is have the count value in each group counting downward

1. find the first blank
count the number of cells below until the next blank
insert that count into this blank cell

2 find the next blank cell and repeat the process of counting downward and inserting the count

I was looking for a formula that I could insert into the blank cells that would insert a downward count


Sample Data

3 <- this was a blank cell but was replaced with the # 3 since there are three rows before the next blank cell
a
a
a

b
b
b
b
b

c
c
c
c

 

[Fluff]

If the data is created by a macro, why no add my code to the end, rather than having a formula?

 

[Danny54]

I ran your code as a vbs script and got this. Perhaps I did something wrong.

Thanks for working with me



[TABLE="width: 141"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]4512577[/TD]
[/TR]
[TR]
[TD]4514601[/TD]
[/TR]
[TR]
[TD]4516115[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]4018073[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]2463332[/TD]
[/TR]
[TR]
[TD]2979596[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]4022619[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]2526302[/TD]
[/TR]
[TR]
[TD]4512486[/TD]
[/TR]
[TR]
[TD]4516033[/TD]
[/TR]
[TR]
[TD]4516065[/TD]
[/TR]
</tbody>[/TABLE]

 

[Danny54]

ok, checked your code again. the blank cells have a interior color of green but contain no data. If i remove the color, your code works. any suggestion on making it work so i don't have to remove the color first?


Thanks

 

[Danny54]

a little more checking.

I tested the code after using the fill with no color button and then ran the macro where-as it failed to see the cell as blank.

If i copied a unused cell to the right into this colored cell and then tested, "Perfect".

So, what is really in this cell and can I check for it? All i can see is the color green.

Thanks

 

[Fluff]

Does this cure the problem?

Sub Danny54()
    Dim rng As Range
    With Range("A2", Range("A" & Rows.Count).End(xlUp))
        .Value = .Value
        For Each rng In .SpecialCells(xlConstants).Areas
            rng.Offset(-1).Resize(1).Value = rng.Count
        Next rng
    End With
End Sub

If not in a blank cell put
=CODE(A2)
Where A2 is one of the blank cells, what does it say?

 

[Danny54]

That worked. So i can understand, what does the .Value = Value do?

Thanks so much! I appreciate the help I get here. You folks are Super. I hope to someday give back as well.

Have a wonderful day.

 

[Fluff]

If you have a formula that returns "" & then copy paste as values you will get a Nullstring in those cells, the .Value=.Value simply makes them truly blank.