1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto hide empty rows for a different sheet, same workbook

Discussion in 'VBA Macros' started by Hema1309, Aug 13, 2017.

  1. Hema1309

    Hema1309 New Member

    Messages:
    9
    Hello everyone,

    I am a newbie to vba code and have created multiple sheets for a work purpose. I am struggling with one portion of it. I need to automatically hide empty rows on a different sheet (same workbook).
    The code I am using is giving me syntax error back and I cant seem to figure it out. Any help is highly appreciated!


    Thanks,
    Hema

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    Please upload a workbook , and explain what you want done.

    Asking members to debug your code is always more problematic , because for one , not everyone wants to go through someone else's code.

    Narayan
  3. Hema1309

    Hema1309 New Member

    Messages:
    9
    Thank you for your respond Narayan.

    The workbook is attached is it massive!

    The code that I am having issue with is I have created this code on PDData sheet and it needs to hide empty rows in FlatStage sheet.

    Code (vb):


    Application.ScreenUpdating = False

    Dim s As String

    For i = 1 To worksheet("FlatStage”).range("A7:A32").AutoFilter 1, "<>", , , False

      s = i & "
    :" & i

      If IsEmpty(Cells(i, 1).Value) Then

      Rows(s).Select

      Selection.EntireRow.Hidden = True

      End If

      Application.ScreenUpdating = False

    Attached Files:

  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    As Excel workbooks go , this is not massive ; thanks for uploading the workbook and the explanation.

    Can you check back after a couple of hours ?

    Narayan
  5. Hema1309

    Hema1309 New Member

    Messages:
    9
    I sure can, it is way past my bedtime but I am sure to be up early to check it. The workbook contains more, I am newbie so if you see anything silly, please ignore it! Again, thank you so much!
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    I could not see any problem in the code ; did you mean the Worksheet_Change procedure ?

    Can you check the attached workbook and clarify / confirm ?

    Narayan

    Attached Files:

  7. p45cal

    p45cal Well-Known Member

    Messages:
    828
    This snippet I think is what your snippet in msg#3 is trying to do:
    Code (vb):
    Application.ScreenUpdating = False
    For Each cll In Sheets("FlatStage").Range("A7:A32").Cells
      cll.EntireRow.Hidden = (Len(cll.Value) = 0)
    Next cll
    Application.ScreenUpdating = False
  8. Hema1309

    Hema1309 New Member

    Messages:
    9
    Narayan,

    What I am trying to do is to hide the empty rows in FlatStage A7:A32 when those rows are empty and when it is not, it needs to display the data. The workbook you sent back to me doesn't seem to be able to do that.
    Yes I meant the Worksheet_Change procedure :)
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    What about the other lines of code in the existing Worksheet_Change procedure ? Should they remain ?

    I was confused because the existing Worksheet_Change procedure does not have the lines of code you posted.

    Narayan
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    See this file.

    Narayan

    Attached Files:

  11. Hema1309

    Hema1309 New Member

    Messages:
    9
    Narayan,

    The other lines needs to remain, the code that is not working is on the very last section on PDData sheet.
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    Have you checked out the file I uploaded most recently ?

    Narayan
  13. Hema1309

    Hema1309 New Member

    Messages:
    9
    Thank you so much Narayan. One portion of your code worked for me and I derived the rest for the sheets. - A little slow, but better than nothing!

    Thank you again!
    The code I landed up using is :
    Code (vb):

    Application.ScreenUpdating = False
      For Each c In Worksheets("FlatStage").Range("A7:A32")
      If c.Value = "" Then
      c.EntireRow.Hidden = True
      Else
      c.EntireRow.Hidden = False
      End If

Share This Page