How to Automatically remove blank rows when selecting from a list (DV)

  • Hi there,
    Here's what i want to do I have a category which is Hotel, Mall & Bank list is "D6" each category has some details - Hotel(A8:A41) ,Mall(A8:A25) Bank(A8:A17) so when i select mall 26:41 is blank, bank 18:41 and i want to hide those blank rows.
    I used this code but it only works for Mall.


    Someone please enlighten me..


    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$D$6")) Is Nothing Then
    With Range("$D$6")
    Range("18:41").EntireRow.Hidden = .Value = "Bank"
    Range("26:41").EntireRow.Hidde = .Value = "Mall"
    End With
    End If
    End Sub



    Thank you so much

  • Re: How to Automatically remove blank rows when selecting from a list (DV)


    Explanation is a touch confusing.


    Is your data (Hotel, Mall & Bank List) on different worksheets? I'm assuming it must be since you quote the range as being in column A in each case so they would all overlap on the same sheet.


    If they are different worksheets, then that code is only designed to work based on a change to your current worksheet. If you wanted to affect the visibility of rows on other worksheets you would have to reference them explicitly.

  • Re: How to Automatically remove blank rows when selecting from a list (DV)


    Quote from TheGlovner;799986

    Explanation is a touch confusing.


    Is your data (Hotel, Mall & Bank List) on different worksheets? I'm assuming it must be since you quote the range as being in column A in each case so they would all overlap on the same sheet.


    If they are different worksheets, then that code is only designed to work based on a change to your current worksheet. If you wanted to affect the visibility of rows on other worksheets you would have to reference them explicitly.



    Help please :-(:-(

  • Re: How to Automatically remove blank rows when selecting from a list (DV)


    Please give this a try and see if that works for you.


  • Re: How to Automatically remove blank rows when selecting from a list (DV)


    Quote from sktneer;800451

    Please give this a try and see if that works for you.




    Thank you for your response!


    I tried executing the code but I'm getting the macro name dialog box whenever i tried to run it.

  • Re: How to Automatically remove blank rows when selecting from a list (DV)


    Remember that this is a Change Event Code which triggers automatically when a cell is changed on the sheet.
    You were supposed to replace the existing change event code with the proposed code.
    And anytime you choose an item from the drop down list in D1, the code will be triggered automatically.


    Refer to the attached.

  • Re: How to Automatically remove blank rows when selecting from a list (DV)


    Quote from sktneer;800457

    Remember that this is a Change Event Code which triggers automatically when a cell is changed on the sheet.
    You were supposed to replace the existing change event code with the proposed code.
    And anytime you choose an item from the drop down list in D1, the code will be triggered automatically.


    Refer to the attached.


    sktneer Thank you! I just figured it out... You save my day :smile::yourock:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!