Empty rows
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)? |
Empty rows
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)? |
Empty rows
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 |
Empty rows
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 |
Empty rows
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? |
Empty rows
"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 --- |
Empty rows
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? |
Empty rows
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. |
Empty rows
... 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 --- |
Empty rows
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 |
Empty rows
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 |
All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com