Unhide/Hide rows based on value on another sheet

slayer1957

New Member
Joined
Jan 9, 2017
Messages
36
Good day,
I have code that prints checklists of equipment with a loop function.

I want to improve on this, the checklist usually print, changing cell A3 each time with a value in Sheet9 cell $I as the heading.

I now want to include a table with options of equipment in columns J-W on Sheet9, if i mark the cell with an "X" it must unhide certain rows which i will specify as range on Sheet1. If there is no "X" the rows must remain hidden.

So if lets say there is an "X" in row J,K,L,M and none in N-W on sheet9, it must unhide specified rows range in sheet1 according to J,K,L,M and rows specified for N-W must remain hidden. Then it must print the first checklist.
Then it will check next row for "X" and hide/unhide according to "X" and print again.

I have some code for the printing which i usually use and started the one for the X but i dont know how to offset each row the whole time as each row is checked for an "X"

Can someone help, can you also explain the offset function with a comment maybe
VBA Code:
    Option Explicit

Public Sub CustomPrintBASED_ON_SELECTION()
  Dim lPrint As Long
    Dim Ans As Variant
    Dim myValue1 As Variant
    Dim myValue2 As Variant
        
        
        
         Ans = MsgBox("  Can I Print The Pages        ", vbYesNo)
        
    Select Case Ans
    Case vbYes
        myValue1 = InputBox("Starting Inspection List Number") 'Input box stating the starting inspection page number
        myValue2 = InputBox("Ending Inspection List Number")    'Input box stating the ending inspection page number
        
        For lPrint = myValue1 To myValue2 'Specify the number of items to print from cells A3-XXXXXXX
 
        [A3] = Sheet9.[$I2].Offset(lPrint - 0, 0) '[A3} starting cell of 1 as mentioned above, is the cell value to change every time on the ActiveSheet, Set the cell value to change in [__]
                                            'Range from Sheet number, cell range in second [__], Offset is the starting row and column
        
        
        If Sheet9.Range("J3").Value = "X" Then      'this portion must also check all cells for an X in the entire row and hide unhide accordingly
        Rows("51:60").EntireRow.Hidden = True
        ElseIf Sheet9.Range("E50").Value = "" Then
        Rows("51:60").EntireRow.Hidden = False
        End If
        
        If Sheet9.Range("K3").Value = "X" Then      'this cell
        Rows("51:60").EntireRow.Hidden = True
        ElseIf Sheet9.Range("E50").Value = "" Then
        Rows("51:60").EntireRow.Hidden = False
        End If
        
        If Sheet9.Range("L3").Value = "X" Then      'etc
        Rows("51:60").EntireRow.Hidden = True
        ElseIf Sheet9.Range("E50").Value = "" Then
        Rows("51:60").EntireRow.Hidden = False
        End If
        
        ActiveSheet.PrintOut
        'ActiveSheet.PrintOut Preview:=True
        Next lPrint
        
      
    Case vbNo
    Exit Sub
    End Select
 
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

slayer1957

New Member
Joined
Jan 9, 2017
Messages
36
VBA Code:
Dim i As Integer
ActiveSheet.Activate
If Not Application.Intersect(Range("C5"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Show only Positive variance %":

            For i = 8 To 17
                    If Range("D" & i).Value "SMALLER THAN" 0 Then
                        Rows(i).EntireRow.Hidden = True
                        Else: Rows(i).EntireRow.Hidden = False
                    End If
            Next i
    
        Case Is = "Show only Negative variance %":
            For i = 8 To 17
                    If Range("D" & i).Value "GREATER OR EQUAL TO" 0 Then
                        Rows(i).EntireRow.Hidden = True
                        Else: Rows(i).EntireRow.Hidden = False
                    End If
            Next i
    
        End Select
End If

Maybe i can use this, but can you define j as an integer
 

slayer1957

New Member
Joined
Jan 9, 2017
Messages
36
VBA Code:
Dim c As Range

Application.ScreenUpdating = False

Application.Calculation = xlManual

For Each c In Range("I14:I62")

    If c.Row Mod 2 = 0 Then

        c.Offset(1).EntireRow.Hidden = Not (c.Value = 2)

    End If

Next
-------------------------------
I want the to specify rows that needs to print, myvalue 1 and 2. I want the vba code to check in all those rows that i have specified in columns J-W, if there is an X in lets say column J it must unhide certain cells, if there is an X in column K it must unhide another set of cells on Sheet 1.

It must go through each row everytime and then print as per my first VBA code.

It must be something like this,

For the range 1-10 of myvalue1 to myvalue 2,
if cells J in column of myvalue1_1 is marked with X, then unhide specific rows on sheet 1, if not do not unhide
if cells K in row of myvalue1_1 is marked with X, then unhide another specific rows on sheet 1, if not do not unhide
if cells L in row of myvalue1_1 is marked with X, then unhide specific rows on sheet 1, if not do not unhide
and so it continues for all rows J-W, unhiding all the rows on sheet 1 and then it will print the checklist.
Then it must hide specific rows again before start of next loop

Then it will loop to next myvalue1-2, checking for X in each column J-W and print and hide and so it continues
 

Forum statistics

Threads
1,147,957
Messages
5,744,042
Members
423,841
Latest member
barren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top