Reference separate sheet but omit blanks in specific columns

[nc_waggoner]

Hi, my goal is to create a reference sheet that only displays rows with valid data. In the below example (Sheet 1), I would like to display the same information on another sheet (Sheet 2) but omit the entire row for samples 4 and 7 due to one of the tests being blank. Does anyone know how to accomplish this?

[TABLE="******* 583"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sample[/TD]
[TD]Test 1[/TD]
[TD]Test 2[/TD]
[TD]Difference[/TD]
[/TR]
[TR]
[TD]8/24/2016[/TD]
[TD]1[/TD]
[TD]35.1[/TD]
[TD]35.7[/TD]
[TD]-0.6[/TD]
[/TR]
[TR]
[TD]8/25/2016[/TD]
[TD]2[/TD]
[TD]34.8[/TD]
[TD]35.3[/TD]
[TD]-0.5[/TD]
[/TR]
[TR]
[TD]8/26/2016[/TD]
[TD]3[/TD]
[TD]36.1[/TD]
[TD]37.5[/TD]
[TD]-1.4[/TD]
[/TR]
[TR]
[TD]8/27/2016[/TD]
[TD]4[/TD]
[TD]35.7[/TD]
[TD] [/TD]
[TD]35.7[/TD]
[/TR]
[TR]
[TD]8/28/2016[/TD]
[TD]5[/TD]
[TD]35.4[/TD]
[TD]36.4[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]8/29/2016[/TD]
[TD]6[/TD]
[TD]34.9[/TD]
[TD]36.2[/TD]
[TD]-1.3[/TD]
[/TR]
[TR]
[TD]8/30/2016[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]37.1[/TD]
[TD]-37.1[/TD]
[/TR]
[TR]
[TD]8/31/2016[/TD]
[TD]8[/TD]
[TD]33.2[/TD]
[TD]34.8[/TD]
[TD]-1.6[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[MUKESHY12390]

Welcome to Board....

Are you looking for VBA or non-VBA solution...?

 

[MUKESHY12390]

if yes... then here is the solution.

Sub test()
Dim I As Long
Dim ws As Worksheet
Dim lstRow As Long
Dim lstCol As Long
Dim sht As Worksheet


Set sht = ThisWorkbook.Worksheets("sheet2")
Set ws = ThisWorkbook.Worksheets("sheet1")
sht.Cells.Clear


lstRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
lstCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column


For I = 1 To lstRow


   If ws.Cells(I, 3) <> "" And ws.Cells(I, 4) <> "" Then '''here you can even more criteria
      ws.Cells(I, 1).Resize(1, lstCol - 1).Copy
      sht.Activate
      sht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
      sht.Paste
      Application.CutCopyMode = False
   End If


Next I


End Sub

 

[nc_waggoner]

Thanks Mukeshy. Macro is a better option so long as I can decipher it enough to understand it. I'm ok at them but not incredibly advanced. A few things:
1. Awesome profile pic. I'm a big Flash fan myself
2. The macro cuts off the Difference column. How can I adjust this? This is important because this was just an example and there are quite a few more columns.
3. The sheet I want to reference will be in a different workbook all together...a sharepoint file to be exact. Any issues you see with that?

 

[MUKESHY12390]

thanks bro..

just change this " ws.Cells(I, 1).Resize(1, lstCol - 1).Copy" to ws.Cells(I, 1).Resize(1, lstCol ).Copy

 

[MUKESHY12390]

3. The sheet I want to reference will be in a different workbook all together...a sharepoint file to be exact. Any issues you see with that?

As long sheet names are same there won't be any issue ....if sheet name gets change then you need to change in code.