To filtering empty cells in A1:A15000 and it works fine.
The case is, A1:F15000 is a LOOKUP formula. So if there's any change or deleted cells in parent sheets, the child sheet is will automatically change.
Is there any way to automatically hide the blank rows on child sheet without ALT + F11 and running VBA?
The sheets that needs to get this VBA command is "RO" and the parent sheet is "Stock detail 07 Feb 2022"
Re: Command to Hide Every Rows that empty automatically
In child sheet, instead of using formula then filter, I would suggest using VBA to copy from parent to child, firing change of column D, U-Y from parent sheet.
Code is placed in sheet even, not module.
PHP Code:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim Lr&, i&, cell As Range, arr() Set ws = Sheets("Stock detail 09 Feb 2022") If Intersect(Target, ws.Range("D:Y")) Is Nothing Then Exit Sub Lr = ws.Cells(Rows.Count, "W").End(xlUp).Row ReDim arr(1 To Lr, 1 To 6) Sheets("RO").Range("A2:A1000000").ClearContents For Each cell In ws.Range("W7:W" & Lr) If cell.Value Like "PDR" And Not cell.Offset(0, 2).Value Like "PDR" Then i = i + 1 arr(i, 1) = cell.Value arr(i, 2) = cell.Offset(0, 2).Value arr(i, 3) = cell.Offset(0, -19).Value arr(i, 4) = cell.Offset(0, -2).Value arr(i, 5) = cell.Offset(0, -1).Value arr(i, 6) = cell.Offset(0, 1).Value End If Next Sheets("RO").Range("A2").Resize(i + 1, 6).Value = arr End Sub
Bookmarks