counting blocks of data
 

this is a sample of a large block of data I have. A block of data is
represented by the letter A until the next cell is Blank. For E.G. row 1
has 2 blocks of data.
the 1st block is row1, col3 and 4 and the next block is column 9.
row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is
the 2nd block.
Row 3 has 1 block only. column 6&7
Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent
cells.

1 2 3 4 5 6 7 8 9
Row 1 A A A
Row 2 A A A A A
Row 3 A A
Row 4
Row 5 A A A A

Thank you all for your anticipated support in this again!!







I have to count each block of data per row.
E.G. Row 1 will have 2 blocks of data

counting blocks of data
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom_mcd" wrote in message
...
this is a sample of a large block of data I have. A block of data is
represented by the letter A until the next cell is Blank. For E.G. row 1
has 2 blocks of data.
the 1st block is row1, col3 and 4 and the next block is column 9.
row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9
is
the 2nd block.
Row 3 has 1 block only. column 6&7
Row 4 has 0 blocks and row 5 has 1 block because all the A's are in
adjacent
cells.

1 2 3 4 5 6 7 8 9
Row 1 A A A
Row 2 A A A A A
Row 3 A A
Row 4
Row 5 A A A A

Thank you all for your anticipated support in this again!!







I have to count each block of data per row.
E.G. Row 1 will have 2 blocks of data

counting blocks of data
 

Assuming your data is in the first 9 columns (A:I) as show, use this formula
on Row 1 to report how many "blocks" exist in Row 1 and copy the formula
down as needed...

=SUMPRODUCT((A1:H1<"")*(B1:I1=""))

--
Rick (MVP - Excel)


"tom_mcd" wrote in message
...
this is a sample of a large block of data I have. A block of data is
represented by the letter A until the next cell is Blank. For E.G. row 1
has 2 blocks of data.
the 1st block is row1, col3 and 4 and the next block is column 9.
row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9
is
the 2nd block.
Row 3 has 1 block only. column 6&7
Row 4 has 0 blocks and row 5 has 1 block because all the A's are in
adjacent
cells.

1 2 3 4 5 6 7 8 9
Row 1 A A A
Row 2 A A A A A
Row 3 A A
Row 4
Row 5 A A A A

Thank you all for your anticipated support in this again!!







I have to count each block of data per row.
E.G. Row 1 will have 2 blocks of data

counting blocks of data
 

This macro should do what you asked. I had to make several assumptions. I
assumed that you don't have a header row and you want to scan all columns for
all rows that have data. I also didn't know what you wanted to do with the
"blocks of data count". So I assumed you wanted to place it in a cell next
to the last column with data in your used range. Hope this helps! If so,
let me know, click "YES" below.

Sub CountBlocks()

Dim LastRow As Long
Dim LastColumn As Long
Dim rw As Long
Dim cell As Range
Dim Blocks As Long

LastRow = Sheets("Sheet1").UsedRange.Rows.Count
LastColumn = Sheets("Sheet1").UsedRange.Columns.Count

For rw = 1 To LastRow
For Each cell In Range(Cells(rw, "B"), Cells(rw, LastColumn))
If Not IsEmpty(cell) And IsEmpty(cell.Offset(, 1)) Then
Blocks = Blocks + 1
End If
Next cell
Cells(rw, LastColumn + 1).Value = Blocks
Blocks = 0
Next rw

End Sub

--
Cheers,
Ryan


"tom_mcd" wrote:

this is a sample of a large block of data I have. A block of data is
represented by the letter A until the next cell is Blank. For E.G. row 1
has 2 blocks of data.
the 1st block is row1, col3 and 4 and the next block is column 9.
row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is
the 2nd block.
Row 3 has 1 block only. column 6&7
Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent
cells.

1 2 3 4 5 6 7 8 9
Row 1 A A A
Row 2 A A A A A
Row 3 A A
Row 4
Row 5 A A A A

Thank you all for your anticipated support in this again!!







I have to count each block of data per row.
E.G. Row 1 will have 2 blocks of data

counting blocks of data
 

Put in q2array enter using CSEcopy down

=SUM((("A"=C2:P2)-("A"=OFFSET(C2:P2,,1))=1)+0)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom_mcd" wrote in message
...
this is a sample of a large block of data I have. A block of data is
represented by the letter A until the next cell is Blank. For E.G. row
1
has 2 blocks of data.
the 1st block is row1, col3 and 4 and the next block is column 9.
row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9
is
the 2nd block.
Row 3 has 1 block only. column 6&7
Row 4 has 0 blocks and row 5 has 1 block because all the A's are in
adjacent
cells.

1 2 3 4 5 6 7 8 9
Row 1 A A A
Row 2 A A A A A
Row 3 A A
Row 4
Row 5 A A A A

Thank you all for your anticipated support in this again!!







I have to count each block of data per row.
E.G. Row 1 will have 2 blocks of data