Count almost empty rows

[David Daugherty]

I am trying to come up with a macro to count the number of almost empty
rows between non empty rows, and I am at a complete loss on how to go
about doing this.

If the next row has anything in the first cell, the D? will be 1, but
if A? is empty for any of the rows below it, D? will be
1+numberOfEmptyCells, and then delete the rows with the empty A.

I am starting with this:
row/col A B C D E F
1 data data data count data data
2 data data data count data data
3 data
4 data
5 data data data count data data
6 data
7 data data data count data data
8 data data data count data data
9 data data data count data data

And I want to end up with:
row/col A B C D E F
1 data data data 1 data data
2 data data data 3 data data
3 data data data 2 data data
4 data data data 1 data data
5 data data data 1 data data
6 data data data 1 data data

Any help with this would be greatly appreciated.

Thanks
David Daugherty

 

[Chip]

does this have to be done with a macro? because you could easily do
this in an excel formula?

 

[David Daugherty]

A formula could work, but since I'm starting with a CSV file I was
thinking that a macro would be easier for the people who would use it
(unfortunatly not the brightest people I have come across).

Of course my thinking could be wrong.

What would the formula be? Maybe with that I can come up with a good
way of doing it, copy and paste maybe.

 

[Tom Ogilvy]

Sub FixDate()
Dim rng as Range
Dim ar as Range
set rng = Columns(2).specialCells(xlBlanks)
for each ar in rng.Areas
ar(1).offset(-1,2).Value = ar.count + 1
Next
rng.EntireRow.Delete
set rng = Columns(4).specialCells(xlBlanks)
rng.Value = 1
End Sub

Code is untested and may contain typos.

 

[David Daugherty]

It works perfectly.

Thank you very much

David