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?

 

[JLGWhiz]

Assuming that your data begins in cell A1

Private Sub Worksheet_Deactivate()
Rows.Hidden = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, r As Range
         Set r = Rows(Target.Row)
         Set rng = Range(Cells(1, 1), Cells(800, 18))
If Target.Rows.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, rng) Is Nothing then        
        rng.EntireRow.Hidden = True
        r.EntireRow.Hidden = False
        r.Select
    End If
Application.EnableEvents = True
End Sub

Both codes go in the same sheet code module. Note that you only get one shot at selecting the row you want to show. As soon as you make a selection within the specified range only the selecte row will be available from that range. You would have to unhide the rows to make a new selection. The rows will automatically unhide when you leave the sheet.

 

[Samhill62]

Assuming that your data begins in cell A1

Private Sub Worksheet_Deactivate()
Rows.Hidden = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, r As Range
         Set r = Rows(Target.Row)
         Set rng = Range(Cells(1, 1), Cells(800, 18))
If Target.Rows.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, rng) Is Nothing then        
        rng.EntireRow.Hidden = True
        r.EntireRow.Hidden = False
        r.Select
    End If
Application.EnableEvents = True
End Sub

Both codes go in the same sheet code module. Note that you only get one shot at selecting the row you want to show. As soon as you make a selection within the specified range only the selecte row will be available from that range. You would have to unhide the rows to make a new selection. The rows will automatically unhide when you leave the sheet.

Thanks for the speedy reply JLGWHIZ, I pasted the code as instructed, however, I get an error message..."Compile Error: Can't find project or library"

 

[JLGWhiz]

Thanks for the speedy reply JLGWHIZ, I pasted the code as instructed, however, I get an error message..."Compile Error: Can't find project or library"

What Excel version are you running and on what platform? Sometimes you can just open the vb editor (ALT + F11), Click on Tool, References and then chech the one that the notice says it can't find. But if you are running an old version and it can't find a reference in a later version then the code will need a rewrite. But I don't know what it errored on.

 

[Samhill62]

What Excel version are you running and on what platform?

Running Excel 2016 on Windows 10.

 

[JLGWhiz]

Running Excel 2016 on Windows 10.

Then it should work as written. The error message should have specified which reference was missing. Open the vb editor (Alt + F11) and click on Tools on the menu bar, then click 'References' and find the one specified in the message and check it. It should then work OK.

 

[btorrean]

Samhill62,

Did you possibly put the code in a standard or class module and not in the actual worksheet's code? That could possibly cause the problem you are experiencing.

Brian

Brian J. Torreano

 

[Samhill62]

Hi Brian, The code is in the worksheet code. I have checked the box for Microsoft Visual Basic for Applications in the References library and if I put a check in any of the VB application references it tells me "Name conflicts with existing module, project or object library".

 

[JLGWhiz]

Hi Brian, The code is in the worksheet code. I have checked the box for Microsoft Visual Basic for Applications in the References library and if I put a check in any of the VB application references it tells me "Name conflicts with existing module, project or object library".

Here is what I show for references in my system
o Visual Basic For Applications
o Microsoft Excel 15.0 Object Library
o OLE Automation
o Microsoft Office 15.0 Object Library

If any of those are missing from your checked items, try locating and checking them to see if that solves it.

 

[Samhill62]

All checked and in same sequence, still recieving the same error message.