Hide all rows both above and below the selected row using VBA

[Samhill62]

I was wondering if anyone has had dealings with hiding rows? My conundrum is, I have a spreadsheet with 800 rows of data, each row consists of 18 columns and when any cell in this array is selected, I would like to automatically hide all of the rows above the selected row and below it. The other trick is, that when I exit the sheet (move to another worksheet in the same workbook), all of the rows automatically unhide. Any ideas?

 

[Joe4]

Do you have any other VBA code in that particular Sheet module?

 

[Samhill62]

None at all, just the code provided.

 

[JLGWhiz]

None at all, just the code provided.

Can you try the code in a new macro enabled workbook? Just enter some phony data to track your rows, for maybe 10 rows and 1 column, then copy the code to the sheet code module and select a cell to see it it does the same thing.

 

[Samhill62]

Can you try the code in a new macro enabled workbook? Just enter some phony data to track your rows, for maybe 10 rows and 1 column, then copy the code to the sheet code module and select a cell to see it it does the same thing.

I opened a new workbook, saved it as a macro enabled workbook and copied a selection of data to it and I still get the same error.

 

[JLGWhiz]

I opened a new workbook, saved it as a macro enabled workbook and copied a selection of data to it and I still get the same error.

Then the only conclusion I can come up with is that it must be something in your system settings that VBA does not like and I have no idea what that might be. I cannot duplicate the error and the code works fine on my system.
Regards, JLG

 

[Logit]

.
.
Here is a copy of the project. https://www.amazon.com/clouddrive/s...7h7bntg6zwXCbch5b1?ref_=cd_ph_share_link_copy

What happens when you run it ?

 

[JLGWhiz]

.
.
Here is a copy of the project. https://www.amazon.com/clouddrive/s...7h7bntg6zwXCbch5b1?ref_=cd_ph_share_link_copy

What happens when you run it ?

It works as expected. If I select a cell, then the row for the selected cell appears as the top row in the Excel window. If I activate another sheet and then return to sheet 1, all rows have been unhidden. No error messages.

I do get an error message when I download the file and open Excel to view it. But that appears to be a web site anomally, since once I clear that message, the second worksheet disappears and everything works normally.

 

[Samhill62]

It works as expected. If I select a cell, then the row for the selected cell appears as the top row in the Excel window. If I activate another sheet and then return to sheet 1, all rows have been unhidden. No error messages.

I do get an error message when I download the file and open Excel to view it. But that appears to be a web site anomally, since once I clear that message, the second worksheet disappears and everything works normally.

Hi JLGWHIZ,

Thanks again for the immense effort. I downloaded the file from the Amazon drive and opened it in Excel. I then went to the code and copied all of it and pasted it into my file. It wored perfectly, what can I say but many thanks for your perserverance.

I would also like to extend my thanks to all of the other guys in the forum who proffered their help. This is why I am on this site as it contains some truly amazingly helpful people. Once again many thanks.