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

 

[Evapar18]

Here is my VBA code... I am embarrassed to even post it.

Application.ScreenUpdating = False
Application.Calculation = xlManual
 
If Sheet1.Range("D6:D609") = "Closed" Then
Rows.Hidden = True
End If

If Sheet1.Range("D6:D609") = "-" Then
Rows.Hidden = True
End If


Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

 

[Joe4]

It looks like you are already using Filters.
So why not just Filter column D to show the "Active" values?
Then all the other rows would be hidden automatically, no code needed.

 

[Evapar18]

It looks like you are already using Filters.
So why not just Filter column D to show the "Active" values?
Then all the other rows would be hidden automatically, no code needed.

Funny... I literally was reading an article on doing just that but doing it with code, then I could have it auto print out the active jobs, which is what I am after, is a printout of the active jobs. Then I would have to turn all the filters back on.

 

[Joe4]

The Macro Recorder is often a useful tool in getting snippets of code like that.
Just turn on the Macro Recorder, and record yourself performing those steps manually, then stop the Recorder.
Sometimes, you will need to do a little "clean-up" on the code, but it will contain much of the "nuts & bolts" you need.

 

[Evapar18]

I am stuck now... I got it to filter out the items I want but the printer prints everything, button included. I just need the table printed out

Dim srcWS As Worksheet
Set srcWS = ThisWorkbook.Sheets("Sheet1")
        
    
    'Criteria Filter
    srcWS.Range("A1:L609").AutoFilter Field:=4, Criteria1:="Active"
  
Application.ScreenUpdating = True

       ' ActiveSheet.PrintOut

Any other suggestions on printing it and getting the rows to come back?

 

[Joe4]

Just set you print area before printing, i.e.

 srcWS.PageSetup.PrintArea = "$A$1:$L609"

And you can remove the filters like this:

 srcWS.ShowAllData

 

[Evapar18]

Thanks for the reply. The Print area is not that large anymore because it only shows the Active rows, which may only be 2, 5, 10 whatever is left. How would you set the print area on a random amount of rows?

 

[Joe4]

Thanks for the reply. The Print area is not that large anymore because it only shows the Active rows, which may only be 2, 5, 10 whatever is left. How would you set the print area on a random amount of rows?

It does not matter if some of the rows in your print area are hidden. It will only print the unhidden ones.
Try it and see!

 

[Evapar18]

It printed 11 pages of nothing and 1 page with the table on it.