3

I would like to know how I could incapsulate the following action in a if() statement, if possible.

I would like my program to check the excel file and see if I have an empty cell in COL A Cell 1 , COL B Cell 1, and COL C Cell 1 lets say, then it should delete that entire row.

My idea (that did not work) was to create ranges for each of these and make an if block out of it, but it didn't work as i would have wanted it to.

xl.Range colA = MySheet.Range["A:A"];      
xl.Range colB = MySheet.Range["B:B"];
xl.Range colC = MySheet.Range["C:C"];

if(colA.SpecialCells(xl.XlCellType.xlCellTypeBlanks) && colB.SpecialCells(xl.XlCellType.xlCellTypeBlanks) && colC.SpecialCells(xl.XlCellType.xlCellTypeBlanks))
            {
                MySheet.Range[MyRange].EntireRow.Delete();
            }

Any idea would be most appreciated seeing as i am stumped. I have also tried making the range into a string, and checking for NullOrEmpty cells, but it didn't work.

Prajwal
  • 3,930
  • 5
  • 24
  • 50
Adi Mohan
  • 117
  • 1
  • 16
  • How did it not work as you expected, did it skip rows by any chance? – BugFinder Jan 09 '17 at 09:08
  • It didn't take any rows at all. – Adi Mohan Jan 09 '17 at 09:09
  • so you just want to remove rows where all 3 cells are blank? you'd probably need to do a for loop.. either that or theres chunks of code missing in your example – BugFinder Jan 09 '17 at 09:11
  • Yes. Basically I want it to check that if in all cells on the same row, the cells are empty, than the entire row should be deleted, as it is useless. In my example, the range "MyRange" is just the entire COL A . – Adi Mohan Jan 09 '17 at 09:14
  • 1
    Possible duplicate! If you want to remove all the empty rows and columns in a worksheet check out my answer to this: [Remove Empty rows and Columns From Excel Files Faster using Interop](http://stackoverflow.com/questions/40574084/remove-empty-rows-and-columns-from-excel-files-faster-using-interop/40726309#40726309) This answer shows how to loop through and delete all the empty rows/cols before the used range starts and any empty rows/cols in the used range. Hope this helps. – JohnG Jan 09 '17 at 14:17
  • @JohnG This is a nice way of removing rows and columns but what I'm trying to do is remove them based on a specific criteria, and that criteria is if in 3 specific columns, the same cell are empty (ex: A1 B1 C1), then and only then should that entire row be removed, otherwise it should not be affected. However I did find your approche awesome and will be using that algo the first chance i get. – Adi Mohan Jan 10 '17 at 05:23
  • @poke I think that you were hasty to mark my question as a duplicate as I did not find anything similar (atleast in my opinion) nor something that actually helped me with my particular case. Also, whatever i have found up to this point, I could not adapt to my personal needs. If I am wrong please feel free to point that out and let me know why that is the case. – Adi Mohan Jan 10 '17 at 08:44
  • @AdiMohan The linked question should give you an idea how to figure out whether cells are empty and how to remove columns/rows. If that does not help you solve your problem, I suggest you to edit your question to show what else you have tried and *why* it didn’t work out and explaining how it failed. – If you make it clear that your question cannot be answered by the linked question, then your question will eventually be reopened. – poke Jan 10 '17 at 09:56
  • @Adi Mohan in your comment _but what I'm trying to do is remove them based on a specific criteria, and that criteria is if in 3 specific columns, the same cell are empty (ex: A1 B1 C1), then and only then should that entire row be removed_ the code I linked too checks EVERY cell. Simply change it to only look for the cells you want (ex: A1 B1 C1). I would think that should be easy to do. – JohnG Jan 10 '17 at 10:15

0 Answers0