0

I am new to this so bear with me. I currently have a google sheet that has formulas in different cells. I want to be able to hide the empty cells if they are blank. Is there a way I can do that? For example, I want to be able to delete all empty cells between B11 and B20.

This is something I have found online but it is not quite doing what I want it to do:

 function adder() {
     var sheet = SpreadsheetApp.getActive().getSheetByName('NY Quote');
     var lastRow = sheet.getLastRow(); 
     for (var i = 1; i < lastRow+1; i++) {

         //var valueB = sheet.getRange('B'+(lastRow-i+1)).getValue() + "";
         if((sheet.getRange('B'+(lastRow-i+1)).getValue())=="") {
             sheet.getRange('B'+(lastRowi+1)).deleteCells(SpreadsheetApp.Dimension.ROWS);
         }
     }
 }

P.S. will the script work if the cells are merged?

Thank you!

Elletlar
  • 3,136
  • 7
  • 32
  • 38
Learning Ahh
  • 7
  • 2

1 Answers1

0

The script in your sample loops from i = 1 to i < lastRow+1 - that is from the first to the last row.

If you want the script to loop from row 11 to row 20 instead - change the loop condition to:

for (var i = 11; i < 21; i++)

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • 1
    Hi! just one more thing. What if I wasn't the same thing between B11:D11? I tried to write it as suchbut it made all the cells blank: if((sheet.getRange('B'+'C'+'D'+(lastRow-i+1)).getValue())=="") { sheet.getRange('B'+'C'+'D'+(lastRow-i+1)).deleteCells(SpreadsheetApp.Dimension.ROWS); } – Learning Ahh Oct 26 '20 at 15:43
  • If any of the cells between `B11:D11` is blank - do you want to delete the specific cell or the whole row? – ziganotschka Oct 26 '20 at 15:51
  • If any of the cells between B11:D11 is blank - I would want to delete the whole row – Learning Ahh Oct 26 '20 at 15:55
  • In this case, you need to create a nested loop ot iterate not only through rows, but also through columns. Have a look a e.g. [this](https://stackoverflow.com/questions/32282187/find-position-of-a-cell-containing-a-specific-string/32289541#32289541) sample, I hope it is helpful for understanding! – ziganotschka Oct 26 '20 at 16:01
  • I tried creating my nested loop but Unfortunately, I still get the same result. You've been of great help. I will keep on trying – Learning Ahh Oct 26 '20 at 17:00