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?
Hide Blank Rows From Bottom Up
-
-
-
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
Perfect, thanks a lot!
-
Re: Hide Blank Rows From Bottom Up
Hello,
How about the following code:
Code
Display MoreSub Macro1() ' ' Macro1 Macro ' Macro recorded 02/23/2007 by Steve ' Dim RowNum As Long Range("A65536").Select Selection.End(xlUp).Select RowNum = ActiveCell.Row + 1 Range("A" & RowNum, "A65536").EntireRow.Select Selection.Activate Selection.EntireRow.Hidden = True End Sub
Steve
(aka sgmunson)
-
Re: Hide Blank Rows From Bottom Up
This also works:
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. -
-
Re: Hide Blank Rows From Bottom Up
You can also write the following very small function
CodePublic 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:
Comment: Using the
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:
Code
Display MoreSub Macro1 Dim MaxRow As Long MaxRow = ActiveSheet.Rows.Count Range(Range("A" & MaxRow).End(xlUp).Offset(1), "A" & MaxRow).EntireRow.Hidden = True End Sub
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 shgThis also works:
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!