Mataupu
Fa'atulagaina o le fa'afitauli
We have a range of cells with data that contains empty cells:
The task is to remove empty cells, leaving only cells with information.
Metotia 1. Talatala ma vave
- Selecting the original range
- Tomi le ki F5, next button faatumutumuga (Faapitoa). I le faamalama e matala, filifili gaogao sela(Gaogao) ma kiliki OK.
All empty cells in the range are selected.
- We give a command in the menu to delete selected cells: right-click- Delete cells (Delete Cells) with upward shift.
Metotia 2: Fa'asologa Fa'asologa
To simplify, let’s name our working ranges using Igoa Pule (Igoa Pule) tab fomula (Formula) or, in Excel 2003 and older, the menu Faaofi - Igoa - Tofi (Faaofi - Igoa - Fa'amatala)
Name the range B3:B10 HaveEmpty, range D3:D10 – NoneEmpty. Ranges must be strictly the same size, and can be located anywhere relative to each other.
Now select the first cell of the second range (D3) and enter this scary formula into it:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“”,ROW(Empty);ROW() + ROWS(There are Empty))); LINE()-ROW(No Empty)+1); COLUMN(There are Empty); 4)))
I le fa'aPeretania o le a fa'apea:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Empty),””,INDIRECT(ADDRESS(SMALL((IF(Empty<>“”,ROW(Empty),ROW() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))
Moreover, it must be entered as an array formula, i.e. press after pasting ulu (as usual) and Ctrl + Shift + Ulufale. Now the formula can be copied down using autocomplete (drag the black cross in the lower right corner of the cell) – and we will get the original range, but without empty cells:
Metotia 3. Galuega fa'apitoa ile VBA
If there is a suspicion that you will often have to repeat the procedure for removing empty cells from ranges, then it is better to add your own function for removing empty cells to the standard set once, and use it in all subsequent cases.
To do this, open the Visual Basic Editor (ALT + F11), fa'aofi se module fou gaogao (menu Fa'aofi - Module) ma kopi le tusitusiga o lenei galuega iina:
Function NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Each Rng In DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(N, 1) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End If End Function
Don’t forget to save the file and switch back from the Visual Basic Editor to Excel. To use this function in our example:
- Select a sufficient range of empty cells, for example F3:F10.
- Alu i le lisi Faaofiofi – Galuega (Fa'aofi - Fa'atino)or click on the button Faaofi galuega (Insert Function) tab fomula (Formula) in newer versions of Excel. In category Faaaogaina e le tagata faaaoga (User Defined) choose our function NoBlanks.
- Specify the source range with voids (B3:B10) as the function argument and press Ctrl + Shift + Ulufaleto enter the function as an array formula.
:
- Deleting all empty rows in a table at once with a simple macro
- Removing all empty rows in a worksheet at once using the PLEX add-on
- Quick fill all empty cells
- What are macros, where to insert macro code in VBA