+ Reply to Thread
Results 1 to 2 of 2

Command to Hide Every Rows that empty automatically

  1. #1
    Registered User
    Join Date
    02-07-2022
    Location
    Bekasi, Indonesia
    MS-Off Ver
    365
    Posts
    26

    Question Command to Hide Every Rows that empty automatically

    So i already had a command on VBA

    Please Login or Register  to view this content.
    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"

    https://drive.google.com/file/d/1t4O...ew?usp=sharing

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    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 Rangearr()
    Set ws Sheets("Stock detail 09 Feb 2022")
    If 
    Intersect(Targetws.Range("D:Y")) Is Nothing Then Exit Sub
    Lr 
    ws.Cells(Rows.Count"W").End(xlUp).Row
    ReDim arr
    (1 To Lr1 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(02).Value Like "PDR" Then
                i 
    1
                arr
    (i1) = cell.Value
                arr
    (i2) = cell.Offset(02).Value
                arr
    (i3) = cell.Offset(0, -19).Value
                arr
    (i4) = cell.Offset(0, -2).Value
                arr
    (i5) = cell.Offset(0, -1).Value
                arr
    (i6) = cell.Offset(01).Value
            End 
    If
        
    Next
        Sheets
    ("RO").Range("A2").Resize(16).Value arr
    End Sub 
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Automatically hide empty columns from Filtered Table based on Totals Row
    By XLn3wb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-21-2015, 07:30 AM
  2. [SOLVED] Hide - Un Hide rows with empty columns
    By BDF in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-14-2014, 02:09 PM
  3. [SOLVED] Macro to automatically hide rows that are empty
    By mcpoogle in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-15-2013, 09:42 AM
  4. Macro command button to hide empty rows
    By djauncey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-31-2012, 10:42 AM
  5. Show and hide rows on command
    By NMarien in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-14-2011, 10:56 AM
  6. Hide Command Button & Rows
    By LaForgeOZ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-01-2008, 08:08 PM
  7. Automatically hide or delete empty rows after IF ?
    By Lost! in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2005, 05:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1