Ave'esea laina gaogao ma koluma i fa'amaumauga

Empty rows and columns can be a pain in tables in many cases. Standard functions for sorting, filtering, summarizing, creating pivot tables, etc. perceive empty rows and columns as a table break, without picking up the data located further behind them. If there are many such gaps, then manually removing them can be very costly, and it will not work to remove all at once “in bulk” using filtering, because the filter will also “stumble” at breaks.

Let’s look at several ways to solve this problem.

Method 1. Search for empty cells

This may not be the most convenient, but definitely the easiest way is worthy of mention.

Suppose we are dealing with such a table containing many empty rows and columns inside (highlighted for clarity):

Suppose we are sure that the first column of our table (column B) always contains the name of a city. Then empty cells in this column will be a sign of unnecessary empty rows. To quickly remove them all, do the following:

  1. Select range with cities (B2:B26)
  2. Tomi le ki F5 ona toe lolomiina lea faatumutumuga (Alu ile Special) or select on the tab Home — Find and Select — Select a group of cells (Home — Find&Select — Go to special).
  3. In the window that opens, select the option Empty cells (Gaogao) ma fetaomi OK – all empty cells in the first column of our table should be selected.
  4. Now select on the tab Itulau Amata faʻatonuga Delete – Delete rows from sheet (Delete — Delete rows) po'o le fetaomi atu i le ala 'oki ctrl+tōʻese – and our task is solved.

Of course, you can get rid of empty columns in exactly the same way, using the table header as a basis.

Method 2: Search for empty rows

As you may have already figured out, the previous method will only work if our data necessarily contains fully filled rows and columns, which can be hooked on when searching for empty cells. But what if there is no such confidence, and the data may contain empty cells as well?

Take a look at the following table, for example, for just such a case:

Here the approach will be a little trickier:

  1. Enter in cell A2 the function COUNT (COUNTA), which will calculate the number of filled cells in the row to the right and copy this formula down to the entire table:
  2. Select cell A2 and turn on the filter with the command Fa'amatalaga – Filifili (Fa'amatalaga - Filifili) po'o le ala pupuu keyboard ctrl+gāoioiga+L.
  3. Let’s filter out zeros by the calculated column, i.e. all rows where there is no data.
  4. It remains to select the filtered lines and delete them with the command Home — Delete -‘ Delete rows from sheet (Home — Delete — Delete rows) po'o le ala pupuu keyboard ctrl+tōʻese.
  5. We turn off the filter and get our data without empty lines.

Unfortunately, this trick can no longer be done with columns – Excel has not yet learned how to filter by columns.

Method 3. Macro to remove all empty rows and columns on a sheet

You can also use a simple macro to automate this task. Press keyboard shortcut Alt+F11 pe filifili mai le tab atinae — Visual Basic (Fa'atupu - Fa'atonu Fa'amatalaga Fa'amatalaga). Afai o tabs atinae is not visible, you can enable it through Faila – Filifiliga – Seti Lipine (Faila - Filifiliga - Fa'asinomaga Lipine).

In the Visual Basic editor window that opens, select the menu command Fa'aofi - Module and in the empty module that appears, copy and paste the following lines:

   Sub DeleteEmpty()      Dim r As Long, rng As Range        'удаляем пустые строки      For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count          If Application.CountA(Rows(r)) = 0 Then              If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r))          End If      Next r      If Not rng Is Nothing Then rng.Delete            'удаляем пустые столбцы      Set rng = Nothing      For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count          If Application.CountA(Columns(r)) = 0 Then              If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r))          End If      Next r      If Not rng Is Nothing Then rng.Delete    End Sub

Close the editor and return to Excel. 

Now hit combination Alt+F8 po o le faamau macros tab atinae. The window that opens will list all macros currently available to you to run, including the macro you just created. DeleteEmpty. Filifili ma kiliki le faamau momoʻe (tamomoe) – all empty rows and columns on the sheet will be instantly deleted.

Metotia 4: Su'esu'ega Mana

Another way to solve our problem and a very common scenario is to remove empty rows and columns in Power Query.

First, let’s load our table into the Power Query Query Editor. You can convert it to a dynamic “smart” one with the keyboard shortcut Ctrl+T or just select our data range and give it a name (for example Faʻamatalaga) in the formula bar, converting to named:

Now we use the command Data – Get data – From table / range (Data – Get Data – From table / range) and load everything into Power Query:

Then everything is simple:

  1. We delete empty lines with the command Home – Reduce lines – Delete lines – Delete empty lines (Home – Remove Rows – Remove empty rows).
  2. Right-click on the heading of the first City column and select the Unpivot Other Columns command from the context menu. Our table will be, as it is technically correctly called, masani – converted into three columns: city, month and value from the intersection of the city and month from the original table. The peculiarity of this operation in Power Query is that it skips empty cells in the source data, which is what we need:
  3. Now we perform the reverse operation – we turn the resulting table back into a two-dimensional one in order to return it to its original form. Select the column with months and on the tab suiga filifili se au koluma Pivot (Suiga — Koluma Pivot). In the window that opens, as a column of values, select the last (Value), and in the advanced options – the operation Do not aggregate (Don’t aggregate):
  4. It remains to upload the result back to Excel with the command Fale — Tapuni ma uta — Tapuni ma uta i… (Aiga — Tapuni&Uta — Tapuni&Uta i…)

  • What is a macro, how does it work, where to copy the text of a macro, how to run a macro?
  • Filling all empty cells in the list with the values ​​of the parent cells
  • Removing all empty cells from a given range
  • Removing all empty rows in a worksheet with the PLEX add-on

Tuua se tali