Hiding Rows based on Cell Value- Multiple Use Document

[Christine Hansen]

As a follow up to a previous thread, I have a Certificate of Analysis (CoA) document that is created for each material lot produced. i have been able to consolidate the number of Excel templates based on previous thread responses. I have one further request that I think is available, but outside the scope of my vba knowledge.

The CoA worksheet contains a Header portion in Rows 1-25. This is populated based on specific customer information. The data summary is contained in columns C (Test Method) to N (Meets Specification), and starts in row 28. The number of rows in the data summary is dependent upon the specific customer specification. An example of the data summary is shown at the bottom of the request.

Here is the question: How can I hide the data summary rows based on an N (column F and a row range of 28 to 42) value of 0?

[TABLE="width: 1243"]
<tbody>[TR]
[TD]Test method[/TD]
[TD]Variable Name[/TD]
[TD]Units[/TD]
[TD]N[/TD]
[TD]Avg[/TD]
[TD]Std Dev[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]TGT[/TD]
[TD]LSL[/TD]
[TD]USL[/TD]
[TD]Meets Spec[/TD]
[/TR]
[TR]
[TD]Method A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Method B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Method C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col><col><col span="3"><col></colgroup>[/TABLE]

 

[Snakehips]

Christine,

Try...

Sub HideN()Application.ScreenUpdating = False


For Each cell In Range("F28:F42")
cell.EntireRow.Hidden = cell = 0
Next cell
End Sub

Hope that helps.

 

[Christine Hansen]

I should have asked in my first thread, but would this be place in the Object for the workheet or separate Module? I tried to place in the worksheet object, but it did not work-compilation error. There is other code in the object, so I am not sure if this is causing the error:

Option Explicit
Sub HideN()Application.ScreenUpdating = False

For Each cell In Range("F28:F42")
cell.EntireRow.Hidden = cell = 0
Next cell
End Sub
Sub Make_PDF()
Dim pdfName As String

pdfName = Range("D14").Text & "_" & Range("D18").Text & "PO_" & Range("D17").Text & "_" & "SO_" & Range("D20").Text & "_" & Format$(Date, "mm-dd-yyyy") + ".pdf"

ChDrive "G:\"
ChDir "\Quality\Finished Product CoAs\Current Year\" & Range("D14").Value

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=pdfName, _
Quality:=xlQualityMinimum, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If WorksheetFunction.CountBlank(Worksheets("Template").Range("D17:D18")) >= 1 Or WorksheetFunction.CountBlank(Worksheets("Template").Range("D20:D21")) >= 1 Then
MsgBox "Print disabled because at least one cell is left blank in Range D17:D18 or Range D20:D21"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountBlank(Worksheets("sheet1").Range("D17:D18")) >= 1 Or WorksheetFunction.CountBlank(Worksheets("sheet1").Range("D20:D21")) >= 1 Then
MsgBox "Close disabled because at least one cell is left blank in Range D17:D18 or Range D20:D21"
Cancel = True
End If
End Sub

 

[Snakehips]

Assuming it is to be run on that one sheet then there should be no issue with the code being in the sheet's code module along with other code.

As for the Compile error.....
For some reason when posting code on the forum, now and again, it removes the carriage return between the Sub's first and second lines.

Sadly, I did not spot that that had occurred this time and so did not edit it .
What rendered as.... Sub HideN()Application.ScreenUpdating = False should be....
Sub HideN()
Application.ScreenUpdating = False

Also, I see that you are using Option Explicit. This means that all variables must be declared and the 'cell' I have used in the loop is such a variable.

So please revise that code to be....

Sub HideN()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("F28:F42")
cell.EntireRow.Hidden = cell = 0
Next cell
End Sub

 

[Christine Hansen]

The code works perfectly, but it does not work with the pdf function. I have a button on the form to run save the document as pdf, but it does not hide the rows when selected- it only performs the pdf. I removed some of the code that was not applicable to this particular template, and this is the screen shot under the Excel Ojject for Sheet 2 (which is the CoA template):


Option Explicit
Sub HideN()
Dim cell As Range
Application.ScreenUpdating = True
For Each cell In Range("F28:F42")
cell.EntireRow.Hidden = cell = 0
Next cell
End Sub
Sub Make_PDF()
Dim pdfName As String

pdfName = Range("D14").Text & "_" & Range("D18").Text & "PO_" & Range("D17").Text & "_" & "SO_" & Range("D20").Text & "_" & Format$(Date, "mm-dd-yyyy") + ".pdf"

ChDrive "G:\"
ChDir "\Quality\Finished Product CoAs\Current Year\" & Range("D14").Value

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=pdfName, _
Quality:=xlQualityMinimum, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

I know this is very close to working the way I would like!

 

[Christine Hansen]

It works! I re-arranged the code lines, and it functions exactly the way I anticipated. Thanks so much for your assistance, as it has made my job of maintaining documents a lot easier. Still not sure how vba works, but each time an expert responds, some of it makes more sense.

Thanks again...