Excel uses blanks to determine data ranges, and a blank row in the wrong place will inhibit many built-in features. But if we already know that there are only 589 items in the whole column then we can confidently tell Excel to exit the For loop at row 590.Blank rows aren’t bad, but in most sheets, they’re definitely undesirable. However this can create an endless loop where “i” will never get to 600 if we keep doing i = i – 1. Hence every time a blank row is deleted, instead of moving onto the next row number (next i), we want to check the same row again to see if it is blank (hence we need i = i – 1). This section is here to account for situations where we may have multiple consecutive blank rows. And this is the row number we need to tell Excel to stop and exit the For loop.
In this case, there are 589 items in the column hence we need 590 (589 + 1). To find out, highlight the relevant column, check the “Count” at the bottom and + 1. In this example the last row of the table is 600 hence we are looping from 1 to 600ī) This number needs to be the total number of items in the column + 1. And of course this can change depending on which row our table starts and ends. See below (B)Ī) In this For loop, “For i = 1 to 600” is telling Excel to loop from number 1 to 600 and because we are putting i in Cells( i, 1), we are looping from row 1 to 600. If i = 590 Then ‘change 590 to the total number of items in Column A and + 1. If it is Column B, it would be Cells(i, 2). In our example, the empty cell is in Column A hence it is Cells(i, 1). If Len (Cells(i, 1)) = 0 Then ‘Cells(row, column).
Copy and paste following into the module:įor i = 1 To 600 ‘change 600 to the last row of the table.Go to Developer tab and open Visual Basic.Highlight all empty rows, right-click and delete:įirst of all we will need to make sure Macro/VBA is enabled.For example in this case, the first five rows: Because the table is currently separated by empty rows, if we do not select the entire column, Excel will think the table ends at the first empty row. Note: it is important highlight the entire column first. Apply filter to the whole table by first highlighting the whole column and click Filter:.If this is what you need, this is the right article for you. What is the best way to remove empty rows in Excel? There is a couple of different ways to do it – the manual way or using VBA.
0 kommentar(er)
