Hide Rows per cell values, print "active" row value, show rows.

[Evapar18]

I have a simple table that has one column that is either "-", "Active" or "Closed" and I want to print just the rows that are listed as active. I know there are half a dozen ways to print the Table Rows\Columns that are "Active". I have searched for this, but I have not been able to piece this together to make it work. I have tried doing it as an if statement, but that is just constantly failing. I tried using a for For\Next statement, but I couldn't get that to work either.

I am thinking that it would be best to hide all the rows that have a value of "-" and "Closed" then print out only the "Active" table contents, finally make the rows be shown, all from clicking a button. (the button part I can figure out) .

Here is the table I made:

Sequential Job_Project Index.xlsm

drive.google.com

Please let me know if the link does not work.

Thanks,
Scott

 

[Joe4]

What is the exact name of your table?
Please post your entire VBA code, as you currently have it.

 

[Evapar18]

I have this running as a macro only nothing else. That's why this is driving me crazy, it seems simple enough but has been everything but that.

Sub Macro2()
'
' Macro2 Macro
'
Dim srcWS As Worksheet
Set srcWS = ThisWorkbook.Sheets("Sheet1")
        
    
'Criteria Filter
srcWS.Range("A1:L23").AutoFilter Field:=4, Criteria1:="Active"

srcWS.PageSetup.PrintArea = "$A$1:$L609"
ActiveSheet.PrintOut
  
srcWS.ShowAllData

Application.ScreenUpdating = True
    
End Sub

Table1:

 

[Joe4]

You are only applying your filter down to row 23, but your print area down to row 609.
That is the cause of those blank sheets (the blank rows from row 24-609).

Why are you setting your print way past the end of your table?

 

[Evapar18]

That's why I asked how am I supposed to set my print area when this will be a dynamic spreadsheet always changing which projects are active and which ones are not.

 

[Joe4]

Try this code, which will dynamically find the last row with data in column A, and use that for your filter and print area:

Sub Macro2()
'
' Macro2 Macro
'
Dim srcWS As Worksheet
Dim lr As Long

Set srcWS = ThisWorkbook.Sheets("Sheet1")
    
'Find last row with data in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'Criteria Filter
srcWS.Range("A1:L" & lr).AutoFilter Field:=4, Criteria1:="Active"

'Set print area and print
srcWS.PageSetup.PrintArea = "$A$1:$L$" & lr
ActiveSheet.PrintOut
  
'Remove filter
srcWS.ShowAllData

Application.ScreenUpdating = True
    
End Sub

 

[Evapar18]

Works really well, but when it hits "srcWS.ShowAllData" it gives this error:

It was giving me that error yesterday, but then some times it would run it... I thought it was something I was doing, but I don't think so.

 

[Joe4]

If filters have already been removed, it may return an error.
You can ignore it like this:

On Error Resume Next
srcWS.ShowAllData
On Error GoTo 0

 

[Evapar18]

If filters have already been removed, it may return an error.
You can ignore it like this:

On Error Resume Next
srcWS.ShowAllData
On Error GoTo 0

I did like you told me in the beginning, record a macro. I did and I used that code into other code and that seemed to work:

'Remove filter
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4

Now just set the button to run the macro and I am all set..

Thank you so much for helping me get this figured out!
Kind Regards,
Scott

 

[Joe4]

You are welcome.

Glad you got everything working the way you need.