Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count almost empty rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count almost empty rows
does this have to be done with a macro? because you could easily do
this in an excel formula? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count almost empty rows
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count almost empty rows
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. -- Regards, Tom Ogilvy "David Daugherty" wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count almost empty rows
It works perfectly.
Thank you very much David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count filled data rows until empty | Excel Discussion (Misc queries) | |||
Empty rows count | Excel Worksheet Functions | |||
How do I sort with empty rows between none empty rows? | Excel Worksheet Functions | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
Can Excel "slide up" rows with content thru empty rows to condense | Excel Worksheet Functions |