Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find next value if rows between are empty? | Excel Discussion (Misc queries) | |||
Deleting All Empty Rows | Excel Discussion (Misc queries) | |||
Delete empty rows with cell type 2 | Excel Discussion (Misc queries) | |||
[excel 2003] Delete empty rows | Excel Worksheet Functions | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |