+ Reply to Thread
Results 1 to 11 of 11

Hide Rows for First Set of Consecutive Blank Cells in Column

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Hide Rows for First Set of Consecutive Blank Cells in Column

    Hi Everyone,

    I have spent the last few hours trying to search the internet/figure this out but for some reason keep running into problems.

    I have a workbook that has years for a stock in Column A and information for that year in Column B. B1 is a header and the rest of B has a vlookup formula pulling data for the relevant year. If the stock did not exist in a year, the cells in B will be blank until the first year of the stock. I want to hide this group of rows and no other rows in the sheet. (There are blank rows below this data for formatting purposes) The solution needs to be VBA. Any guidance is greatly appreciated!

    Cheers,
    Lisa

    AAPL High
    1976
    1977
    1978
    1979
    1980
    1981 0.6
    1982 0.6

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Many thanks to both of you!! They both worked perfectly

  5. #5
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Stnkynts,

    After I hide the rows, I have code to print the sheet and then loop for the next ticker to do the same. Is there a way to unhide what I just hid? I tried using ws.Range("A" & sRow, "A" & lRow).EntireRow.Hidden = False, but that didn't work.

    It gives me an error on this line:
    lRow = c.Offset(-1).Row

    Thanks again,
    Lisa
    Last edited by pastuslm; 04-22-2015 at 01:35 PM.

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Can you show the code that you are running, in it's entirety.

  7. #7
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Sub PrintPESheet()
    Dim myRange As Range, MyVal As Range
    Dim FilePath As String

    'Turn off screen updating
    Application.ScreenUpdating = False
    FilePath = "C:\RESEARCH\PE SHEET MASTER\BB - Hi and Low -- PE Sheets.xlsm"

    'Opens PE SHEET MASTER without showing it
    Dim wb2 As Excel.Workbook
    Workbooks.Open Filename:="C:\RESEARCH\PE SHEET MASTER\BB - Hi and Low -- PE Sheets.xlsm", Password:="", ReadOnly:=True, UpdateLinks:=True
    ActiveWindow.Visible = False
    Application.ScreenUpdating = True

    Set wb2 = Workbooks("BB - Hi and Low -- PE Sheets.xlsm")
    Dim wb1 As Excel.Workbook
    Set wb1 = ThisWorkbook
    Dim model As Excel.Worksheet
    Set model = ThisWorkbook.Sheets("Model")

    'Sets range of tickers
    Set myRange = wb1.Sheets("Model").Range("AM4:AM31")

    'Finds last row of data in the PE SHEET
    Dim lastCell As Long
    lastCell = wb2.Sheets("PE SHEET (VL & BB)").Range("A" & Rows.Count).End(xlUp).Row


    'Sets the print area of the PE SHEET and fits it to one page
    With wb2.Worksheets("PE Sheet (VL & BB)").PageSetup
    .PrintArea = "A1:W" & lastCell
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
    End With

    Dim b As Boolean: b = False
    Dim sRow As Long, lRow As Long
    Dim c As Range
    Dim wb2s As Worksheet: Set wb2s = wb2.Sheets("PE SHEET (VL & BB)")


    'Finds the tickers that need to be printed, indicated by "X"
    For Each MyVal In myRange
    If MyVal > 0 Then
    If MyVal.Cells.Offset(, 1).Value = "X" Then
    MyVal.Cells.Offset(, 1).Value = ""
    wb2s.Range("A1:A44").EntireRow.Hidden = False
    wb2.Sheets("PE Sheet (VL & BB)").Range("A1").Value = MyVal.Value

    'Hides empty rows in the PE SHEET and then prints each sheet
    For Each c In wb2s.Range("B1:B" & wb2s.Range("B" & Rows.Count).End(xlUp).Row)
    If b = False Then
    If Len(c) = 0 Then
    sRow = c.Row
    b = True
    End If
    ElseIf b = True Then
    If Not Len(c) = 0 Then
    lRow = c.Offset(-1).Row
    Exit For
    End If
    End If
    Next c

    wb2s.Range("A" & sRow, "A" & lRow).EntireRow.Hidden = True

    wb2.Sheets("PE Sheet (VL & BB)").PrintOut Copies:=1

    'Unhides the rows that were just hidden

    End If

    End If
    Next MyVal


    'Deletes the "X"'s from the print list and closes the PE SHEET MASTER without saving any changes
    wb2.Sheets("PE Sheet (VL & BB)").Range("B2").Value = ""
    wb2.Close savechanges:=False

    End Sub

  8. #8
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    For future reference please use code tags around your code.

    This line should unhide the row in question
    Please Login or Register  to view this content.
    If it errors, there are many things that could be causing an issue. I don't have time at the moment to disect your code. Someone else might.

  9. #9
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Thanks for all your help. Unfortunately, that's what I tried earlier with no luck. I'll mess around with it some more.

  10. #10
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    Actually, I'm sure the unhide code works. It's the hide code that doesn't work for multiple tickers. I had originally tried it out with just one. It can't seem to loop back to the next ticker and hide blank rows. I'll try to understand better what you sent me, and I'm sure I'll eventually figure it out.

  11. #11
    Registered User
    Join Date
    08-10-2012
    Location
    San Jose, California
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hide Rows for First Set of Consecutive Blank Cells in Column

    I figured it out. I had this in the wrong spot:

    Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    Dim b As Boolean: b = False
    Dim sRow As Long, lRow As Long
    Dim c As Range

+ 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. Count number of consecutive non blank cells in a column macro excel
    By nasim12w in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-03-2015, 06:00 AM
  2. [SOLVED] Hide Rows if the cell in a certain column is blank or zero
    By Shannon561 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-16-2014, 07:00 AM
  3. [SOLVED] Find consecutive non blank cells in a column
    By desibabuji in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 11-08-2013, 12:50 PM
  4. [SOLVED] Hide any rows 5 through 16 in which column H is blank
    By Triscia in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-14-2013, 04:30 PM
  5. Replies: 4
    Last Post: 07-05-2012, 12:05 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