VBA to update and remove empty rows

[Jet Fusion]

Hi

Please can someone assist.

I am trying to make sheet2 update from sheet1 that when sheet1 updates to either yes or no -
yes = it will show on sheet2
no = will not show on sheet2

It's updating but showing empty rows which I do not want but also sheet2 needs to update whenever sheet1 does have info changed

So if I enter on sheet1 yes for all then all should show but if some have yes and some no then it should only show the yes info but also if I add any other or change from yes to no it should automatically update the info on sheet2 with no empty rows even if sheet1 has empty rows.

Thank you

 

[p45cal]

=FILTER(Sheet1!G2:H7,Sheet1!J2:J7="Yes","None")

 

[Marc L]

Hello, according to the attachment a VBA event procedure to paste only to the Sheet2 worksheet module :​

Private Sub Worksheet_Activate()
        UsedRange.Clear
   With Sheet1
       .[A1,G1:H1].Copy [A1]
       .[Z2].Formula = "=J2=""Yes"""
       .UsedRange.AdvancedFilter 2, .[Z1:Z2], [A1:C1]
       .[Z2].Clear
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Jet Fusion]

Thank you @p45cal and @Marc L will try both out, much appreciated.

 

[Jet Fusion]

Hi @p45cal and @Marc L

I tried out the above and for -

@p45cal - I get #Value error = I may have put it in wrong

@Marc L - It worked in the temp file but not in my workbook. Also, I have other info on my worksheet that I do not want cleared can your VBA perhaps work the same but only copy and update the info and not clear the sheet2 when copied. I don't need the A1 you reference in your code

I copied the rows in the same place as my workbook due to sensitive info I can't share the exact file.

In the attached updated file I'm only looking at the info on sheet1 G4:H500 to show on Sheet2 but then to update as per original post. @Marc L I have your code in the file.

Thanks

 

[p45cal]

@p45cal - I get #Value error = I may have put it in wrong

Formula at cell B11 of the attached.

 

[Jet Fusion]

@p45cal Thank you that worked

The only thing I forgot to mention in my worksheet that column D & E are merged as per last attached file when I use the formula it gives a spill and shows the spill column D but if I unmerge it works perfectly.

Is there a way to have it so that I can still keep the two cells merged



Thank you again.

 

[Marc L]

It worked in the temp file but not in my workbook.

As guessing can't be coding, if you are not able to fit any help to your real workbook then​

such attachment not reflecting exactly the real workbook is a no sense, just a time waste …​

​

According to your post #5 attachment the revamped VBA event procedure :​

Private Sub Worksheet_Activate()
        [B3].CurrentRegion.Offset(1).Clear
   With Sheet1
       .[Z2].Formula = "=K4=""Yes"""
       .[B3].CurrentRegion.AdvancedFilter 2, .[Z1:Z2], [B3:D3]
       .[Z2].Clear
   End With
End Sub

You may Like it !​

​

 

[Jet Fusion]

As guessing can't be coding, if you are not able to fit any help to your real workbook then​

such attachment not reflecting exactly the real workbook is a no sense, just a time waste …​

​

According to your post #5 attachment the revamped VBA event procedure :​

Private Sub Worksheet_Activate()
        [B3].CurrentRegion.Offset(1).Clear
   With Sheet1
       .[Z2].Formula = "=K4=""Yes"""
       .[B3].CurrentRegion.AdvancedFilter 2, .[Z1:Z2], [B3:D3]
       .[Z2].Clear
   End With
End Sub

You may Like it !​

​

Thank you and sorry for wasting your time...

 

[p45cal]

column D & E are merged as per last attached file

I can't find any merged cells in that attachment.

Is there a way to have it so that I can still keep the two cells merged

I doubt it; what exactly are you hoping to show? Perhaps a file closer to your actual file?

 

[Jet Fusion]

@p45cal thank you so much I manage to get it to work and works great much appreciate your time and patience