I am running a macro to hide all rows that have a value of "0" and print the rest. So it does just that very well however it hides the last row and I can't figure out why. So for example I have 10 rows with data, only 9 rows print. Please help because I can't figure it out.
Any additional questions I would be more then happy to clarify anything.
Please Login or Register to view this content.
Last edited by Richard Buttrey; 04-24-2013 at 06:53 PM.
Hi,
Try changing
Set rng = Sheets("Test Reg UC").Range("b11:b63") to
Set rng = Sheets("Test Reg UC").Range("b11:b64")
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thank you for the quick response however when I changed that I have received the error "run time error '9': subscript out of range
I think it is because the top part of the sheet and the bottom part of the sheet is locked so the only rows that can be edited are b11:b63. I didn't think that would be important however it appears for this solution it is.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
Since you're new here I'll change it for you on this occasion but please note for the future.
Do you mean that you have 10 cells all with no zero values in column B between rows 11:63 inclusive?
Can I suggest that rather than use a looping macro to evaluate cells since loops are inherently slow, particularly with large data sets, that you use a Data Filter with a criteria range containing zero to hide the relevant rows. Data filtering is the fastest way I know to do this sort of stuff and is both quicker and easier to code since it needs just one line of code.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thank you for the change, I normally do not post of forums so I am new to this.
To explain further I have locked data(titles and such) from row 1 to row 10. Starting at row 11 column B I have dates. If there is no further information then the cell is blank. Then starting at row 64 I have calculations for the data entered in the rows. So what I needed was a macro that would only print rows 1:10; any data that I manually entered between rows 11:63 and then the calculations from 64:68. So any rows between 11:63 are set to hide so that the printout isn't cluttered with blank rows.
Hi,
As I said. By far the best approach is to use a Data Filter. I'd be inclined to create a helper column which clearly identifies by using a standard Excel function or combination of functions, which rows should be hidden. My usual technique is to have the result of the helper column formuale be either "Hide" or "Show". Then it's a simple one line piece of macro code, along the lines of
which will display just the rows with the word "Show" in the A1:A100 range. Obviously change the A1:A100 to reflect your helper column range.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks