Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(ISBLANK for multiple cells A1:N1
I have read many posts regarding this issue and I thought I wrote my formula
correctly. I have tried it as a formula AND as an array formula =IF(ISBLANK(A1:N1),"Delete","OK") When I use this formula, whether the range has something in it or not, it returns "OK". The spreadsheet is an exported report from a mailing/shipping software program. It has 600 rows, A:N, some with only one Cell containing data. I need to delete all the blank rows (I've read all those posts too). I'm either very thick in the head or there is some invisible data lurking in the cells? I've even reversed the formula =IF(ISBLANK(A1:N1),"OK","Delete"). Arrrggg! Thank you, scrowley(AT)littleonline.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(ISBLANK for multiple cells A1:N1
Try this instead.
=IF((COUNTA(A1:N1)=0),"DELETE","OK") Hope that helps. "SCrowley" wrote in message ... I have read many posts regarding this issue and I thought I wrote my formula correctly. I have tried it as a formula AND as an array formula =IF(ISBLANK(A1:N1),"Delete","OK") When I use this formula, whether the range has something in it or not, it returns "OK". The spreadsheet is an exported report from a mailing/shipping software program. It has 600 rows, A:N, some with only one Cell containing data. I need to delete all the blank rows (I've read all those posts too). I'm either very thick in the head or there is some invisible data lurking in the cells? I've even reversed the formula =IF(ISBLANK(A1:N1),"OK","Delete"). Arrrggg! Thank you, scrowley(AT)littleonline.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(ISBLANK for multiple cells A1:N1
Try this:
=IF(COUNTA(A1:N1)=0,"Delete","OK") If the result is "OK" but you can't see anything in any cells then that means you have invisible data lurking. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... I have read many posts regarding this issue and I thought I wrote my formula correctly. I have tried it as a formula AND as an array formula =IF(ISBLANK(A1:N1),"Delete","OK") When I use this formula, whether the range has something in it or not, it returns "OK". The spreadsheet is an exported report from a mailing/shipping software program. It has 600 rows, A:N, some with only one Cell containing data. I need to delete all the blank rows (I've read all those posts too). I'm either very thick in the head or there is some invisible data lurking in the cells? I've even reversed the formula =IF(ISBLANK(A1:N1),"OK","Delete"). Arrrggg! Thank you, scrowley(AT)littleonline.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(ISBLANK for multiple cells A1:N1
Hi,
Not sure what you want to do but it sounds like you want to delete all the rows that are blank between columns A and N. A macro would be the "correct" way to go. A cheap solution would be to insert a helper row to re-sort the rows. Assuming that column O is empty, enter 1 in O1 and copy down all the rows, increasing the number for each row. Then sort all the data to get all the blank rows together. Delete the blank rows and then use column O to re-sort the rows to their orignal order and delete column O. Might this work? -- Ken Hudson "SCrowley" wrote: I have read many posts regarding this issue and I thought I wrote my formula correctly. I have tried it as a formula AND as an array formula =IF(ISBLANK(A1:N1),"Delete","OK") When I use this formula, whether the range has something in it or not, it returns "OK". The spreadsheet is an exported report from a mailing/shipping software program. It has 600 rows, A:N, some with only one Cell containing data. I need to delete all the blank rows (I've read all those posts too). I'm either very thick in the head or there is some invisible data lurking in the cells? I've even reversed the formula =IF(ISBLANK(A1:N1),"OK","Delete"). Arrrggg! Thank you, scrowley(AT)littleonline.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(ISBLANK for multiple cells A1:N1
THANK YOU! You and Gaurav both suggested =IF(COUNTA(A1:N1)=0,"Delete","OK")
and it worked perfectly! I'm also going to try Ken's suggestion for some other data I need to sort out. I really appreciate this community! -- Thank you, scrowley(AT)littleonline.com "T. Valko" wrote: Try this: =IF(COUNTA(A1:N1)=0,"Delete","OK") If the result is "OK" but you can't see anything in any cells then that means you have invisible data lurking. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... I have read many posts regarding this issue and I thought I wrote my formula correctly. I have tried it as a formula AND as an array formula =IF(ISBLANK(A1:N1),"Delete","OK") When I use this formula, whether the range has something in it or not, it returns "OK". The spreadsheet is an exported report from a mailing/shipping software program. It has 600 rows, A:N, some with only one Cell containing data. I need to delete all the blank rows (I've read all those posts too). I'm either very thick in the head or there is some invisible data lurking in the cells? I've even reversed the formula =IF(ISBLANK(A1:N1),"OK","Delete"). Arrrggg! Thank you, scrowley(AT)littleonline.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(ISBLANK for multiple cells A1:N1
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "SCrowley" wrote in message ... THANK YOU! You and Gaurav both suggested =IF(COUNTA(A1:N1)=0,"Delete","OK") and it worked perfectly! I'm also going to try Ken's suggestion for some other data I need to sort out. I really appreciate this community! -- Thank you, scrowley(AT)littleonline.com "T. Valko" wrote: Try this: =IF(COUNTA(A1:N1)=0,"Delete","OK") If the result is "OK" but you can't see anything in any cells then that means you have invisible data lurking. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... I have read many posts regarding this issue and I thought I wrote my formula correctly. I have tried it as a formula AND as an array formula =IF(ISBLANK(A1:N1),"Delete","OK") When I use this formula, whether the range has something in it or not, it returns "OK". The spreadsheet is an exported report from a mailing/shipping software program. It has 600 rows, A:N, some with only one Cell containing data. I need to delete all the blank rows (I've read all those posts too). I'm either very thick in the head or there is some invisible data lurking in the cells? I've even reversed the formula =IF(ISBLANK(A1:N1),"OK","Delete"). Arrrggg! Thank you, scrowley(AT)littleonline.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF AND ISBLANK | Excel Worksheet Functions | |||
Blank cells: ISBLANK = FALSE, ISTEXT = TRUE | Excel Worksheet Functions | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
If(ISBLANK) | Excel Worksheet Functions |