VBA Macro for hiding and unhiding rows is rearranging cells

[JasonCExcel]

I have 2 sheets... on each sheet there is a macro that hides and unhides rows. Sheet 2 contains all the data to be hidden or unhidden.

When I code 2 runs to hide an entire section, and then code 1 runs to change the amount of rows (this is in unhidden sections) all of the hidden rows reappear and they are out of order. How can I run these two codes with the rows showing up again?

Code 1 - This code hides an entire section of rows entire section of rows:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If Target.Address = "$D$10" Then
If Target.Value = "3" Then
Sheets("Criteria Scoring").Rows("43:78").EntireRow.Hidden = True
Else
Sheets("Criteria Scoring").Rows("43:78").EntireRow.Hidden = False
End If
End If
End Sub


___________________________
This code repeats to to hide individual lines in each section (the correct

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


If Range("D7").Value = 1 Then
Rows("9:17").EntireRow.Hidden = True
Else
Rows("9:17").EntireRow.Hidden = False
If Range("D7").Value = 2 Then
Rows("10:17").EntireRow.Hidden = True
Else
Rows("10:17").EntireRow.Hidden = False
If Range("D7").Value = 3 Then
Rows("11:17").EntireRow.Hidden = True
Else
Rows("11:17").EntireRow.Hidden = False
If Range("D7").Value = 4 Then
Rows("12:17").EntireRow.Hidden = True
Else
Rows("12:17").EntireRow.Hidden = False
If Range("D7").Value = 5 Then
Rows("13:17").EntireRow.Hidden = True
Else
Rows("13:17").EntireRow.Hidden = False
If Range("D7").Value = 6 Then
Rows("14:17").EntireRow.Hidden = True
Else
Rows("14:17").EntireRow.Hidden = False
If Range("D7").Value = 7 Then
Rows("15:17").EntireRow.Hidden = True
Else
Rows("15:17").EntireRow.Hidden = False
If Range("D7").Value = 8 Then
Rows("16:17").EntireRow.Hidden = True
Else
Rows("16:17").EntireRow.Hidden = False
If Range("D7").Value = 9 Then
Rows("17:17").EntireRow.Hidden = True
Else
Rows("17:17").EntireRow.Hidden = False

End If
End If
End If

 

[dmt32]

This was indeed helpful, it solved the issue.

The only outstanding item is that I need to expand this code to hide other criteria groups.

Hi,

Whilst pleased suggest code resolves your initial request, forgive me if I sound a little bemused, I based suggested solution on your post# 5

This entirety of my code. No sort functions or anything else is in play.

Which I took to mean that what you had posted was complete in all you were trying to do & asking the forums assistance with – but now you say after I and others have attempted to provided help, that your initial request now needs expanding?

It is important when asking forum for help that you provide a complete picture of what it is you are trying to do as solutions provided by members (who are all volunteers) may end up not being appropriate or require extensive reworking.

With regard to your additional request, I have no immediate thoughts if code as written, can be adapted to include the extra functionality.

Can I suggest that you post a copy of your worksheets (not pictures) but using MrExcel Addin XL2BB - Excel Range to BBCode

or better still, place copy of your workbook with dummy data on a file sharing site like Dropbox & provide a link to it with clear explanation in all you want to resolve.

I am not around much next few days but will have a look & see if I (and maybe others) can assist you further.



Dave

 

[JasonCExcel]

Apologies Dave. When I posted the original code I had fully coded out sheet 2 and I had created code for one criteria group to test run to see if I was on the right track on sheet one, which led me here when I came across the sorting issue that you solved for me. In truth it was the entire code I had because I hadn't gotten around to trying additional criteria group.

There are 6 total criteria groups and I need sheet 2 to show anywhere from 3 to 6 groups based on the number selected on sheet one. This information was not made clear on my original post and I apologize for that. Unfortunately my administrative settings prevent me from using the XLBB add-on.

 

[dmt32]

There are 6 total criteria groups and I need sheet 2 to show anywhere from 3 to 6 groups based on the number selected on sheet one.

Hi
don't have lot of time but you can see if this update to the code will do what you want

Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsCriteriaScoring As Worksheet
    Dim Groups            As Variant, m As Variant

    'group no's
    Groups = Array(3, 4, 5)
 
    On Error GoTo myerror
 
    Set wsCriteriaScoring = ThisWorkbook.Worksheets("Criteria Scoring")
 
    If Target.Address = "$D$10" And Len(Target.Value) > 0 Then
 
        m = Application.Match(Val(Target.Value), Groups, 0)
        If Not IsError(m) Then
            Application.EnableEvents = False
            With wsCriteriaScoring
              'unhide all groups
                .Rows("43:78").EntireRow.Hidden = False
                'hide required groups
                .Rows(Choose(m, "43:78", "55:78", "68:78")).EntireRow.Hidden = Val(Target.Value) = Groups(m)
            End With
        End If
    
    End If
 
myerror:
    Application.EnableEvents = True
End Sub

Note Option Base 1 statement at the top of the code - This MUST be placed at the very TOP of your worksheets code page OUTSIDE any procedure

Update not tested and a little crude but hopefully, will work for you & allow you to figure out to add other groups if needed

Dave

 

[JasonCExcel]

Sorry for the delay. This worked perfectly. I appreciate all your help and time.

 

[dmt32]

Sorry for the delay. This worked perfectly. I appreciate all your help and time.

no worry solution not that elegant but glad it does what you want & appreciate your feedback

Dave