Unhide the next four rows

[agalvan]

Hi,

I have a workbook that keeps track of deals as they come in. Multiple people use this workbook and for simplicity sake, I built it so that in order to add the next deal in the portfolio you just unhide the next four rows. I'm trying to write a macro that will automate this process. I have something started, but it only unhides 1 row.

'Private Sub Button356_Click Macro_Click()
Dim i As Long
i = 4
Do Until Rows(i).Hidden = True
i = i + 4
Loop
Rows(i).Hidden = False
End Sub

Please let me know where I'm messing up. Thank you!

[mikeTRON]

The problem is you need to identify a starting point, unless you intend to ALWAYs unhide rows 1-4.

You can either find the last used row (which is likely what I would do, assuming this logic works for you) or have the user select the row above the 4 you want to unhide.

[stnkynts]

Are you trying to unhide rows from a constant static row or is it dynamic such as the activecell row or the next hidden row

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[agalvan]

A more descriptive user story would be this: first deal is in lines 9-12, deal two is in 13-16 etc... Lets assume deal 1 and 2 have come in and a user is coming in to the work book to add inputs for the 3rd deal. I am hoping they can just click the button and the next 4 rows will unhide. And for this to be sustainable so that when the next deal comes another person can come in and click the same button and an additional four rows unhide. Does that make sense? Thank you all for your help.

[mikeTRON]

Right, and what we are asking is how do you determine what the START and/or END point of the unhide is.

Please Login or Register  to view this content.

This would work assuming you want to find the last row of used data in column A.

[agalvan]

So full disclosure, I've gotten to this point but piecewising recorded macros and reading other posts. It seems as the original attempt I posted unveils every 4th row that is hidden. Is there anyway to add a line to my code that will include the previous 3 lines? I can post a workbook if it will help out.

[agalvan]

here's a sample workbook

[mikeTRON]

I would just change my above code to look in column H then, since it will have data IF they need to unhide more rows.

Please Login or Register  to view this content.

This works by looking for the lowest name in Column E then unhides using THAT as the starting point. This assumes there is nothing but name then 3 blank cells beneath it before the next name. I tested it and it works on your spreadsheet.


Let us know if you have any questions.

[agalvan]

If I copy this code directly, pull up my macros and hit "run" nothing happens. Am I doing something wrong?

[mikeTRON]

I would say so yes.

So open the visual basic editor (ALT+F11).
Insert Module (as the workbook you attached as a sample did NOT have a module.

Copy and paste the code I posted above including the "option explicit" part.
Close the editor.
Right click on the Button 365 you have on the worksheet (you want to run the macro) then choose Assign Macro. Choose thw one you just created.
Now the button is linked to THAT macro.

When you click the button you will run it. In order for the macro to actually DO anything, you have to have a name in column E in the last set of rows visible.


I attached what I mean so you can see by example.
Unhide_Resize_Offset example.xlsm

[agalvan]

When I do that in the sample workbook I created, it works. When do all of the above in the actual workbook which really is no different besides the fact that it has additional tabs the editor pops up and "Sub UnhideRows()" is highlighted in yellow.

[mikeTRON]

Do you have macros enabled in the real workbook?

[agalvan]

Yes I do. The only difference is that there is some additional "stuff" in my real workbook starting in row 287 that I didn't include in the sample. I don't really think that this should effect the macro though. Does it?

[agalvan]

Actually I did a test, and when I added the rows to the sample workbook the macro no longer runs. Sorry I should have included that stuff in my original.

[mikeTRON]

Ha, yeah it does affect it, if it affects the LastRow calculation.

Do you know how to step through code? If not thatis very useful (as you would determine that the last row calculation is coming up with # 4500 instead of 33 for example.

I suggest printing this as it is very useful.
http://www.cpearson.com/excel/DebuggingVBA.aspx



So are you saying there is data in column E in your real workbook below the reporting?

[agalvan]

Yes there is data in column E. Would the solution be to add a line of code that makes the macro only applicable to rows 9 - 285 or something like that?