Check out this example, with one spare column and three kinds array formulas.

Range `A1:B100`

is filled with random strings, with blank cells,
range `C1:C100`

is just some numbers (could be strings as well).

Range `H1:H100`

is used to as a helper column,
which actually filters out and accumulates the row numbers with non-empty cells in `A`

and `B`

.

`H1`

: array formula to find the first row number with non-empty cells in `A`

and `B`

columns:

```
=MATCH(1,SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))),0)
```

`H2`

: another array formula to find all the rest row numbers with non-empty cells in `A`

and `B`

columns:

```
=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",MATCH(1,SIGN(LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))),0)+H1)
```

The formula is quite long, so just in case, here it is, formatted to show the structure:

```
IF(
ROW()>
SUM(
SIGN(
LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))
)
)
,""
,MATCH(1,
SIGN(
LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))
*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))
)
,0
)+H1
)
```

Type in the last kind of array formula in `D1`

:

```
=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",INDEX($A$1:$C$100,$H1,COLUMN()-COLUMN($D$1)+1))
```

copy the cell to `E1`

, `F1`

; select and copy range `D1:F1`

;
select range `D2:F100`

and paste copied cells.

The formulas could be much shorter at expence of more helper columns.

**Obligatory note:** to enter
the array formula,
press `Ctrl`+`Shift`+`Enter`
instead of just `Enter` for scalar formula).

hiderows, notexcluderows. – athos Apr 17 '17 at 15:48manuallycopy, the whole question's purpose is to do things with excel formula to avoid anymanualcopy or paste or filter. – athos Apr 17 '17 at 16:02