If A1 contains no data (i.e., is empty), how can I make sure that all
other cells in row A contain no data (are empty)?

Hi Gary

A is a column, not a Row.
1 is a Row
If you want to check that column A is empty then either
=COUNTA(A:A)=0 will return True if it is empty or false if it isn't
or
=COUNTIF(A:A,"<")=0
will do the same.

If you really did want a row, then
=COUNTA(1:1)=0

Obviously the formula cannot be placed in column A, or in row 1 for this
to work
--
Regards

Roger Govier


"GARY" wrote in message
oups.com...
If A1 contains no data (i.e., is empty), how can I make sure that all
other cells in row A contain no data (are empty)?

Hi Roger,

I'll revise my question:

If cell A1 is empty, how can make sure that cells B1 through IV1 are
also empty?

Gary

Sub clearall()
If ActiveSheet.Range("A1") = "" Then
Range("1:1").Cells.ClearContents
End If
End Sub


Gord Dibben MS Excel MVP


On 7 Sep 2006 16:51:25 -0700, "GARY" wrote:

Hi Roger,

I'll revise my question:

If cell A1 is empty, how can make sure that cells B1 through IV1 are
also empty?

Gary

Hi all,

Gord's suggestion looks like it clears the contents of non-empty cells.
But that's not what I want to do. Here's the situation:

=======================================

The first "key"-data is in A1 and its related data was originally in
B3, C2, D1, E4 and F5.

To get A1's related data also on row 1, I moved:

B3 up to B1
C2 up to C1
D1 wasn't moved (since it was already on row 1)
E4 up to E1
F5 up to F1

========================================

The next "key"-data is in A7 and its related data was originally in B6,
C7, D9, E8 and F10.

To get the A7's related data also on row 7, I moved:

B6 down to B7
C7 wasn't moved (since it was already on row 7)
D9 up to D7
E8 up to E7
F10 up to F7

========================================

Now, I want to make sure that I didn't miss moving any related data.

What formula can I put in G1 thru Gnnn to catch any non-empty cells in
the rows between each occurrence of "key"-data?

"GARY" wrote:
.. The first "key"-data is in A1 and
its related data was originally in B3, C2, D1, E4 and F5.
To get A1's related data also on row 1, I moved:
B3 up to B1
C2 up to C1
D1 wasn't moved (since it was already on row 1)
E4 up to E1
F5 up to F1

Assuming the source data above is in sheet: X
we can try something like this in another sheet
to auto-"move" the values within rows 1 to 5 in cols B to F up to row1

In a new sheet:

Put in A1, then array-enter the formula,
ie press CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=IF(ISNA(MATCH(TRUE,X!A$1:A$5<"",0)),"",INDEX(X!A $1:A$5,MATCH(TRUE,X!A$1:A$5<"",0)))

Copy A1 across to F1 which effectively "moves":

B3 up to B1
C2 up to C1
D1 wasn't moved (since it was already on row 1)
E4 up to E1
F5 up to F1

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Gord's suggestion was in response to your revised question

If cell A1 is empty, how can make sure that cells B1 through IV1 are
also empty?

Gord's macro looks at A1 and if it is empty, runs code that "makes sure" that
B1 through IV1 are empty by clearing them

If you had a different request, perhaps you should have posted that.


Gord Dibben MS Excel MVP


On 7 Sep 2006 21:53:25 -0700, "GARY" wrote:

Hi all,

Gord's suggestion looks like it clears the contents of non-empty cells.
But that's not what I want to do. Here's the situation:

=======================================

The first "key"-data is in A1 and its related data was originally in
B3, C2, D1, E4 and F5.

To get A1's related data also on row 1, I moved:

B3 up to B1
C2 up to C1
D1 wasn't moved (since it was already on row 1)
E4 up to E1
F5 up to F1

========================================

The next "key"-data is in A7 and its related data was originally in B6,
C7, D9, E8 and F10.

To get the A7's related data also on row 7, I moved:

B6 down to B7
C7 wasn't moved (since it was already on row 7)
D9 up to D7
E8 up to E7
F10 up to F7

========================================

Now, I want to make sure that I didn't miss moving any related data.

What formula can I put in G1 thru Gnnn to catch any non-empty cells in
the rows between each occurrence of "key"-data?

The first "key"-data is in A1 and its related data was originally in
B3, C2, D1, E4 and F5.

To get A1's related data also on row 1, I moved:

B3 up to B1
C2 up to C1
D1 wasn't moved (since it was already on row 1)
E4 up to E1
F5 up to F1

========================================

The next "key"-data is in A7 and its related data was originally in
B6, C7, D9, E8 and F10.

To get the A7's related data also on row 7, I moved:

B6 down to B7
C7 wasn't moved (since it was already on row 7)
D9 up to D7
E8 up to E7
F10 up to F7

========================================

Now, I want to make sure that I didn't miss moving any related data.

What formula can I put in G1 thru Gnnn to catch any non-empty cells in
the rows between each occurrence of "key"-data?

In G1, try
=IF(AND($A1="",COUNTA($B1:$F1)0), "Look here","")
and copy down.

... with the plethora of responses received, the barn-door is still standing
? <g throw us some feedback before this thread fades away ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Ideally, after moving the data in A1's related cells, all of A1's
related cells should be in row 1.
Ideally, after moving the data in A7's related cells, all of A7's
related cells should be in row 7.

I do NOT want to clear the cells in rows 2 thru 6.
I want to find cells on these rows that contain data (so I can move
these cells, too).

Gary

Did you try my shot at this in the other branch ? Thought it returned results
similar to what you're re-describing below.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GARY" wrote:
Ideally, after moving the data in A1's related cells, all of A1's
related cells should be in row 1.
Ideally, after moving the data in A7's related cells, all of A7's
related cells should be in row 7.

I do NOT want to clear the cells in rows 2 thru 6.
I want to find cells on these rows that contain data (so I can move
these cells, too).

Gary