none
How can I creat A button in Excel Sheet to hide Empty Rows in the sheet and unhide them when needed RRS feed

  • Question

  • Dear Sirs,

    I need to add create a Button in an Excel Sheet to Hide Empty Rows in tables and unhide them  when needed to add new records.

    Can you please help

    Thanks

    Nabil karma

    Saturday, June 23, 2012 2:20 PM

Answers

  • Hi Nabil,

    Have you resolved your issue? If you did, please share your solution in the Forum,it will be beneficial for other community members who have the same questions. Thanks.

    In addition, I would give a solution to hide and display the empty rows in Excel by using Macro. At first, assign the Sub Hide_Row() for your button.

    Sub Hide_Row()
     Dim r As Range
     Dim r1 As Range
     Set r = Range("A1").CurrentRegion
     r.AutoFielder Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="qnt"
     Set r1 = r.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count).Cells.SpecialCells(xlCellTypeVisible)
    ActiveSheet.AutoFilterMode = False
    r1.Rows.Hidden = True
    End Sub


    Then, run the Sub Unhide_Row() when you add new data.

    Sub Unhide_Row()
    Dim j As Long
    j = Range("B1").End(xlDown).Row
    Rows("2:" & j).Hidden = False
    End Sub

    Hope this can help you.

    best regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, July 3, 2012 9:08 AM
    Moderator

All replies

  • Hi Nabil,

    Thanks for posting in the MSDN Forum.

    Based on your issue,could you please tell us in which way you desire the functionality in Excel,marco button or creating a button in VSTO? It's beneficial to us for further research if you clarify.Thanks for your cooperation.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 26, 2012 9:18 AM
    Moderator
  • Hi Nabil,

    Have you resolved your issue? If you did, please share your solution in the Forum,it will be beneficial for other community members who have the same questions. Thanks.

    In addition, I would give a solution to hide and display the empty rows in Excel by using Macro. At first, assign the Sub Hide_Row() for your button.

    Sub Hide_Row()
     Dim r As Range
     Dim r1 As Range
     Set r = Range("A1").CurrentRegion
     r.AutoFielder Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="qnt"
     Set r1 = r.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count).Cells.SpecialCells(xlCellTypeVisible)
    ActiveSheet.AutoFilterMode = False
    r1.Rows.Hidden = True
    End Sub


    Then, run the Sub Unhide_Row() when you add new data.

    Sub Unhide_Row()
    Dim j As Long
    j = Range("B1").End(xlDown).Row
    Rows("2:" & j).Hidden = False
    End Sub

    Hope this can help you.

    best regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, July 3, 2012 9:08 AM
    Moderator