Yiyọ awọn sẹẹli ofo kuro ni sakani kan

Ilana ti iṣoro naa

We have a range of cells with data that contains empty cells:

 

The task is to remove empty cells, leaving only cells with information.

Ọna 1. Ti o ni inira ati ki o yara

  1. Selecting the original range
  2. Tẹ bọtini naa F5, next button saami (Pataki). Ninu ferese ti o ṣii, yan Awọn sẹẹli ofo(Ofo) ki o si tẹ OK.

    Yiyọ awọn sẹẹli ofo kuro ni sakani kan

    All empty cells in the range are selected.

  3. We give a command in the menu to delete selected cells: right-click- Delete cells (Delete Cells) with upward shift.

Ọna 2: Agbekalẹ Arun

To simplify, let’s name our working ranges using Orukọ Alakoso (Oluṣakoso orukọ) taabu agbekalẹ (Fọmula) or, in Excel 2003 and older, the menu Fi sii - Orukọ - Fi sii (Fi sii - Orukọ - Ṣetumọ)

 

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)))

Ninu ẹya Gẹẹsi yoo jẹ:

=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 Tẹ (as usual) and Konturolu + Yi lọ yi bọ + Tẹ. 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:

 

Ọna 3. Aṣa iṣẹ ni 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), fi titun kan sofo module (akojọ Fi sii - Module) ati daakọ ọrọ iṣẹ yii nibẹ:

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:

  1. Select a sufficient range of empty cells, for example F3:F10.
  2. Lọ si akojọ aṣayan Fi sii - Iṣẹ (Fi sii - Iṣẹ)or click on the button Fi sii iṣẹ (Insert Function) taabu agbekalẹ (Fọmula) in newer versions of Excel. In category Itumọ Olumulo (Ṣitumọ olumulo) choose our function NoBlanks.
  3. Specify the source range with voids (B3:B10) as the function argument and press Konturolu + Yi lọ yi bọ + Tẹto 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

 

Fi a Reply