How to copy data to new sheet with criterias

[noppph]

Hello Mr.Excel

This is my third question,

I have to fill the form and filter only the people who pass my condition to new sheet.

My conditions are...
1. If the all fields are blank, do not copy them to "Output" sheet.
2. If the salary is less than 30, do not copy them to "Output" sheet.

I want to have a VBA macro to do this job for me.

Here's is my input in the sheet name "Input"
[TABLE="class: grid, width: 254"]
<tbody>[TR]
[TD]Your Name[/TD]
[TD]Nopp[/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Blood[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Salary[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Comment[/TD]
[TD]I'm learning Excel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Your Name[/TD]
[TD]Mr.Excel[/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Blood[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Salary[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Comment[/TD]
[TD]I'm here[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Your Name[/TD]
[TD]Superman[/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Blood[/TD]
[TD]Unknown[/TD]
[/TR]
[TR]
[TD]Salary[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Comment[/TD]
[TD]I'm strong[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Your Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blood[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comment[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here's is my expected result in the sheet name "Output"
[TABLE="class: grid, width: 333"]
<tbody>[TR]
[TD]Your Name[/TD]
[TD]Age[/TD]
[TD]Blood[/TD]
[TD]Salary[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Nopp[/TD]
[TD="align: right"]15[/TD]
[TD]O[/TD]
[TD="align: right"]100[/TD]
[TD]I'm learning Excel[/TD]
[/TR]
[TR]
[TD]Mr.Excel[/TD]
[TD="align: right"]50[/TD]
[TD]B[/TD]
[TD="align: right"]500[/TD]
[TD]I'm here[/TD]
[/TR]
</tbody>[/TABLE]


Expected image:

File link:
https://drive.google.com/file/d/0B0K0o7i934rxT09tcVVlQVlfRDA/view?usp=sharing

 

[pidriu]

I would do something like this im asuming your data is on those cells but you can change it

Sub example ()

Worksheets.Add.Name = "output"
For k = 2 to 500
n= 2
If Cells( k ,2) <> "" And Cells (k, 4) > "30" Then
Sheetoutput.Cells(n , 1) = sheetnotoutput.Cells(k,2)
Sheetoutput.Cells(n , 2) = sheetnotoutput.Cells(k,3)
Sheetoutput.Cells(n , 3) = sheetnotoutput.Cells(k,4)
n= n + 1
End sub

 

[noppph]

I would do something like this im asuming your data is on those cells but you can change it

Sub example ()

Worksheets.Add.Name = "output"
For k = 2 to 500
n= 2
If Cells( k ,2) <> "" And Cells (k, 4) > "30" Then
Sheetoutput.Cells(n , 1) = sheetnotoutput.Cells(k,2)
Sheetoutput.Cells(n , 2) = sheetnotoutput.Cells(k,3)
Sheetoutput.Cells(n , 3) = sheetnotoutput.Cells(k,4)
n= n + 1
End sub

Dear pidriu,

Could you please explain why "k" is need to specify for 2 to 500 ?

Thanks

 

[pidriu]

K will be 2 3 4 5 6 (rows)
i set it up from 2 because i assumed you have headlines and to 500 because you need a limit i normally use this as my limits

LastrowOutput = Sheets("Output").Range("A100000").End(xlUp).Row

so instead of 500 i put lastrowOutput