Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Rosehill - ExcelForums.com
 
Posts: n/a
Default Counting empty cells within a range of cells

A sheet can have a variable range of adjacent cells each containing
the value =1
The range defines the perimeter of, or surrounds a group of empty
cells however outside the perimeter, the remaining cells on the sheet
are empty as well.

The task is to count the empty cells that are surrounded by the
perimeter of 1’s:

To do this manually, I use the @countif (range=0) function however it
requires that I go into the worksheet and select the range manually.

I would like to do this by formula or macro.

The knowns a
1) The location of the start/end cell of the range
2) That the next cell in the range will be above, below, left or
right of its neighbour but we never know which.
3) I think to define the range, I need to to search for the location
of all cells having the value of 1

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

IF: Your corner of the 'perimeter' is known, and your 1's are only on a
rectangular perimeter, then you could use the formula

=COUNTBLANK(OFFSET(C3,0,0,COUNT(C:C),COUNT(3:3)))

where C3 is your upper left corner.

If your perimeter snakes around, then you would require VBA, and some fairly
complex coding.

What exactly is it that you are trying to do?

HTH,
Bernie
MS Excel MVP

"Rosehill - ExcelForums.com" wrote in
message ...
A sheet can have a variable range of adjacent cells each containing
the value =1
The range defines the perimeter of, or surrounds a group of empty
cells however outside the perimeter, the remaining cells on the sheet
are empty as well.

The task is to count the empty cells that are surrounded by the
perimeter of 1’s:

To do this manually, I use the @countif (range=0) function however it
requires that I go into the worksheet and select the range manually.

I would like to do this by formula or macro.

The knowns a
1) The location of the start/end cell of the range
2) That the next cell in the range will be above, below, left or
right of its neighbour but we never know which.
3) I think to define the range, I need to to search for the location
of all cells having the value of 1



  #3   Report Post  
Rosehill - ExcelForums.com
 
Posts: n/a
Default

As you say, the perimeter more often than not snakes around and is not
an orderly rectangle so the manual process is actually
countblank(range 1)+countblank (range 2) etc until all of the cells
are identified.

I envisage that VB code to do this would involve offseting (2,1) from
start /finish cell. This would identify the first blank cell in the
irregular range that is now surrounded by conceivably up to 8 blank
cells. Getting this far in code and even counting the blank cells
around the offset is no problem for me but then writing code to move
through the range that is bounded by the perimeter of 1's and
identify all of the blank cells is the problem. Yes I think the code
is complex. Its kind of like a search, identify and count mission
where the boundry of the search is a perimeter of ones.

Rosehill

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Eliminate empty cells in data range Stephen Excel Discussion (Misc queries) 1 April 2nd 05 04:00 AM
Applying formula to only NON-EMPTY cells in range Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM
counting cells in a range Charles Excel Discussion (Misc queries) 5 February 19th 05 12:59 AM
CountIF cells are not empty Wayne Excel Discussion (Misc queries) 3 January 6th 05 05:44 PM
Sumproduct ... Empty Cells vs Spaces? Ken Excel Discussion (Misc queries) 9 December 17th 04 09:03 PM


All times are GMT +1. The time now is 11:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"