Hide Blank Rows From Bottom Up

  • The following is code that I have to hide all the rows beneath the last row containing data. The only problem is it hides that last row of data also. I don't want it to hide that last row of data, how can I make it not?

    Code
    Sub Macro1()
        Dim Blnk As Long
        Blnk = Range("A65536").End(xlUp).Row
        Range("A65536", Cells(Blnk, 1)).EntireRow.Hidden = True
    End Sub

    Ed
    [SIZE=6]*ficus*[/SIZE]
    Thanks to all for making this website work!

  • Re: Hide Blank Rows From Bottom Up


    Add a +1 to the end of this line like so. You have it looking for the last row, but the last row is data, so you need to add 1 for the first blank row.
    Hope this helps.


    Blnk = Range("A65536").End(xlUp).Row +1

  • Re: Hide Blank Rows From Bottom Up


    Hello,


    How about the following code:



    Steve
    (aka sgmunson)
    :) :) :)

  • Re: Hide Blank Rows From Bottom Up


    This also works:

    Code
    Range(Range("A65536").End(xlUp).Offset(1), "A65536").EntireRow.Hidden = True


    SGMunson, thanks for jumping in to help. We try to avoid as much as possible using Selects (of sheets, ranges, or cells) in macros. It's very handy during debugging, but it slows execution -- so typically, you might have Selects in separate lines of code, and then comment them out when done.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Hide Blank Rows From Bottom Up


    You can also write the following very small function


    Code
    Public Function MaxRow() As Long
    'Returns the number of the real last row 
    'that contains something in the active sheet
        On Error Resume Next
        MaxRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    End Function


    which you will find generally very helpfull in many applications,


    and then select rows-to-hide like the following code:


    Code
    Range(Cells(MaxRow() + 1, 1), Cells(ActiveSheet.Rows.Count, 1)).EntireRow.Hidden = True


    Comment: Using the

    Code
    ActiveSheet.Rows.Count

    you don't have to worry about changing code if excel 8 has 3 million rows.

  • Re: Hide Blank Rows From Bottom Up


    Thanks to everyone in helping me learn the ropes and reasons behind various coding methods. It would appear that one could become version independent with the following macro:



    I suppose if you just wanted one line of code you could substitute in the count phrase where MaxRow appears in the final line and eliminate the other two lines altogether...


    Steve
    (aka sgmunson)
    :) :)


    Quote from shg

    This also works:

    Code
    Range(Range("A65536").End(xlUp).Offset(1), "A65536").EntireRow.Hidden = True


    SGMunson, thanks for jumping in to help. We try to avoid as much as possible using Selects (of sheets, ranges, or cells) in macros. It's very handy during debugging, but it slows execution -- so typically, you might have Selects in separate lines of code, and then comment them out when done.

Participate now!

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