remove blank rows

[apgmin]

In sheet 1, say I have 4 rows of data

Now In sheet 2, I want only those rows in which the a column is not blank ( like A2, A3, A4, A5)are not blank

Now when I link the cells if there are say 3 rows which are blank there in sheet 1 then I also get the same 3 rows as zero or blank in sheet 2,

My question is if we can remove these blank lines and make a continuous table or rows in sheet 2

although column f is supposed to be in the next sheet, But I get the same issue

What I need is that if cell a3, a4 a5 is blank, then in the next sheet the row of 3,4 & 5 should be removed and I should get a table like the following

thanks in advance

 

[Peter_SSs]

Are you looking for a formula solution, macro or power query?

 

[apgmin]

a formula solution will be preffered as then I can use it later in other ways after understanding what you have done

I saw the filter option in office 365 but I use excel 2013

 

[footoo]

Are you looking for a formula solution, macro or power query?

Or manually with a few mouse clicks.

 

[apgmin]

Or manually with a few mouse clicks.

if it can be done with a few clicks it is also OK but remember there are over 800 rows and of these we need to remove around 200. SO to prevent mistakes a formula will be more convinient

 

[Peter_SSs]

a formula solution will be preffered

22 06 11.xlsm
	A	B	C	D	E	F
1	select	name				name
2	yes	a				a
3						g
4						r
5						y
6	yes	g
7	yes	r
8	yes	y
List

Cell Formulas
Range	Formula
F2:F8	F2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$1000)/(B$2:B$1000<>""),ROWS(F$2:F2))),"")

 

[Peter_SSs]

if it can be done with a few clicks it is also OK

Select column B
Data ribbon tab -> Filter
Click the filter drop-down in B1 and uncheck Blanks -> OK

Select the columns of names -> Copy (Ctrl+C)

Move to the other sheet and Paste

 

[apgmin]

wow works perfectly

THANKS

 

[footoo]

Alternatively :

Select column A
Home>editing>find&select>goto>special>blanks>OK
Right click and select Delete>entire row>OK