Find and extract text in a column of blank Cells

Hi All,

Good day to all.

How can i extract a text in a column of blank cells. Example, A1:A100 is the range, in A14 there is a text say AAA, how can i extract that text and put in cell B1.

Thanks in advance.

Maybe you can delete the empty rows

Sub Delete_empty_rows_in_collumnA_with_Range()

With Range("A20:a2000").SpecialCells(xlCellTypeBlanks).Cells
    .EntireRow.Delete Shift:=xlUp
  End With
  If Err.Number <> 0 Then MsgBox "There are no or only empty cells"
 End Sub

Dear All,

I got it anyway, thanks!

i used this formula

=INDEX(A1:A20,MAX(IF(A1:A20<>"",ROW(A1:A20))))

Array formula CTRL+SHIFT+ENTER