G
GARY
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)?
other cells in row A contain no data (are empty)?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Roger Govier
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
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
G
GARY
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
I'll revise my question:
If cell A1 is empty, how can make sure that cells B1 through IV1 are
also empty?
Gary
G
Gord Dibben
Sub clearall()
If ActiveSheet.Range("A1") = "" Then
Range("1:1").Cells.ClearContents
End If
End Sub
Gord Dibben MS Excel MVP
If ActiveSheet.Range("A1") = "" Then
Range("1:1").Cells.ClearContents
End If
End Sub
Gord Dibben MS Excel MVP
G
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?
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?
M
Max
GARY said:.. 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 ..
G
Gord Dibben
Gord's suggestion was in response to your revised question
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
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
M
MyVeryOwnSelf
The first "key"-data is in A1 and its related data was originally in
In G1, try
=IF(AND($A1="",COUNTA($B1:$F1)>0), "Look here","")
and copy down.
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.
M
Max
... with the plethora of responses received, the barn-door is still standing
? <g> throw us some feedback before this thread fades away ..
? <g> throw us some feedback before this thread fades away ..
G
GARY
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
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
M
Max
Did you try my shot at this in the other branch ? Thought it returned results
similar to what you're re-describing below.
similar to what you're re-describing below.