TOP
# Filling empty cells in the list

### Related Articles:

Cross copying (merging) of data into one column

For filtering, sorting, summarizing or creating pivot tables, it is necessary to have a list without spaces, that is, a table without empty rows or cells.

Therefore, it is often necessary ** fill the empty cells of the table with values from the upper cells ** , example:

with | do |

It is quite simple to do.

Select the range in the first column that needs to be filled (in our case it is A2:A10).

Go to the menu Edit - GoTo - Special (or press the F5 key) and in the window that appears, select Blanks :

Without deselecting the range, enter the equal sign (=) in the first cell and click on the cell ** A2 ** (that is, make a reference to the previous cell):

To enter this formula into all selected (empty) cells, press Ctrl + Enter instead of the usual Enter.

That's all! Simple and beautiful.

And, finally, we advise you to replace all entered formulas with values, because during sorting or adding/removing rows, the correctness of the formulas may be broken. Select all the cells in the first column, copy them and paste them back using Paste Special in the context menu, select an option Values .

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"