Remove blank rows from a column

Question

I am in search of a formula that can help me remove blank rows from a column. I tried different formulas, but with no luck.

Apreciate the help.

I cant use sort because the imput data changes. I tried different formulas found online, but didnt work.

Answer 1

Here is a way for those using Excel 2013:

---

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$15)/(A$2:A$15<>""),ROWS(B$1:B1))),"")

---

Or MS365 Users can use TOCOL() or FILTER()

---

=TOCOL(A2:A15,1)

---

Or,

=FILTER(A2:A15,A2:A15<>"")

---