Macro to hide rows with a certain cell empty then set print area and print

[tahrah]

I just did a simple macro by using the record function. I am trying to
create a macro that hides certain columns, then on the columns that are
left, it hides the rows of records with no information in a certain
field. Then it sorts the remaining items and resets the print area,
then prints. This is what I've come up with so far but it DOES NOT
hide the rows of records with no information in the cell (cell would be
Column Q). Also, it's resetting the print area but the printout is
still coming out BLANK???? I appreciate any help you can offer.

Sub Open_Quotes()
'
' Open_Quotes Macro
' Macro recorded 1/5/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Rows("3:5").Select
Selection.EntireRow.Hidden = True
Columns("A:I").Select
Selection.EntireColumn.Hidden = True
Columns("K:K").Select
Selection.EntireColumn.Hidden = True
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Columns("V:AM").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 440
ActiveWindow.ScrollRow = 511
ActiveWindow.ScrollRow = 579
ActiveWindow.ScrollRow = 636
ActiveWindow.ScrollRow = 704
ActiveWindow.ScrollRow = 851
ActiveWindow.ScrollRow = 923
ActiveWindow.ScrollRow = 1001
ActiveWindow.ScrollRow = 1119
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1413
ActiveWindow.ScrollRow = 1452
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1538
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 1584
ActiveWindow.ScrollRow = 1609
ActiveWindow.ScrollRow = 1631
ActiveWindow.ScrollRow = 1656
ActiveWindow.ScrollRow = 1677
ActiveWindow.ScrollRow = 1702
ActiveWindow.ScrollRow = 1717
ActiveWindow.ScrollRow = 1731
ActiveWindow.ScrollRow = 1742
ActiveWindow.ScrollRow = 1752
ActiveWindow.ScrollRow = 1759
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1788
ActiveWindow.ScrollRow = 1802
ActiveWindow.ScrollRow = 1817
ActiveWindow.ScrollRow = 1831
ActiveWindow.ScrollRow = 1842
ActiveWindow.ScrollRow = 1852
ActiveWindow.ScrollRow = 1860
ActiveWindow.ScrollRow = 1867
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1892
ActiveWindow.ScrollRow = 1902
ActiveWindow.ScrollRow = 1913
ActiveWindow.ScrollRow = 1928
ActiveWindow.ScrollRow = 1942
ActiveWindow.ScrollRow = 1953
ActiveWindow.ScrollRow = 1967
ActiveWindow.ScrollRow = 1974
Rows("7:2001").Select
Range("A2001").Activate
Selection.Sort Key1:=Range("Q7"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("S22").Select
ActiveWindow.LargeScroll Down:=5
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 139
Range("A6:U143").Select
Range("U143").Activate
ActiveSheet.PageSetup.PrintArea = "$A$6:$U$143"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$6:$U$143"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Range("B6").Select
End Sub

 

[Jim Cone]

The following modified code has a small chance it might be close
to what you want. However, you had better check that print area
before you turn your printer loose.
If the proper rows and columns are hidden, why set a print area?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Open_Quotes()
' Open_Quotes Macro
' Macro recorded 1/5/2007 by Tahrah Hunt
' Keyboard Shortcut: Ctrl+Shift+Q
Dim lngRow As Long
Rows("3:5").EntireRow.Hidden = True
Columns("A:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:M").EntireColumn.Hidden = True
Columns("T:T").EntireColumn.Hidden = True
Columns("V:AM").EntireColumn.Hidden = True
Rows("7:2001").Sort Key1:=Range("Q7"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
lngRow = Range("Q2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = Rows("7:" & lngRow - 1).Address '?
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Range("B6").Select
End Sub
'----------------


"tahrah" <[email protected]>
wrote in message
I just did a simple macro by using the record function. I am trying to
create a macro that hides certain columns, then on the columns that are
left, it hides the rows of records with no information in a certain
field. Then it sorts the remaining items and resets the print area,
then prints. This is what I've come up with so far but it DOES NOT
hide the rows of records with no information in the cell (cell would be
Column Q). Also, it's resetting the print area but the printout is
still coming out BLANK???? I appreciate any help you can offer.

-snip-

 

[tahrah]

Jim - Thank you for such a quick response. I just plugged in what you
typed out directly because I don't know about these things. It did
something. But it was very fast and nothing printed. So I'm not sure
what it did actually LOL. How do I run the macro step by step to find
out where it's stopping?

Also can I please ask you a couple more questions? I sure would
appreciate your help.

Which part of the macro is hiding the rows where column Q is empty?
AND, after it hides all rows where column Q is empty, how would I make
it also hide all rows where column U states "order received"?

I appreciate all of your help. You are a life saver!!

Regards,
Tahrah

 

[Jim Cone]

Tahrah,
The code line that does the printing was commented out by
placing an apostrophe at the beginning of the line.
That tells Excel to ignore that line.

You can "step" thru the code by placing your curser somewhere
in the code and pressing the F8 key. A single line of code will
run each time the key is pressed.

You can determine what the print area is by inserting a message box
right after the print area is set...

ActiveSheet.PageSetup.PrintArea = Rows("7:" & lngRow - 1).Address '?
MsgBox Rows("7:" & lngRow - 1).Address ' <<< Message box inserted
With ActiveSheet.PageSetup

Blank cells sort to the bottom, so starting at the last row of the range
you can tell Excel to look upward to find the first cell with data, get the row
number of that cell and add 1 to it with ...
lngRow = Range("Q2001").End(xlUp).Row + 1

The code then hides all rows starting with lngRow down to row 2001.
-------------

Lifesaver ? - Actually, I am just analyzing your swimming stroke. <g>
It is time for bed.
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



Jim - Thank you for such a quick response. I just plugged in what you
typed out directly because I don't know about these things. It did
something. But it was very fast and nothing printed. So I'm not sure
what it did actually LOL. How do I run the macro step by step to find
out where it's stopping?

Also can I please ask you a couple more questions? I sure would
appreciate your help.

Which part of the macro is hiding the rows where column Q is empty?
AND, after it hides all rows where column Q is empty, how would I make
it also hide all rows where column U states "order received"?

I appreciate all of your help. You are a life saver!!

Regards,
Tahrah

 

[tahrah]

Jim,

Thank you thank you thank you. It works great. I hope you are still
available to help with one more thing.

If I want to hide all rows where column U says "order received", how
would I do that? I would want it to do that before setting the print
area. So the macro should hide all rows where column Q is empty and
all rows where column U says "order received".

Also, isn't there a way to create a button on the worksheet for the
user to click and automatically run this macro?

I appreciate your help. YES! Life Saver!

Regards,
Tahrah

 

[Jim Cone]

Tahrah,
The code between the dotted lines below is an approximation
of what you might need to hide the additional rows.
Also, Chip Pearson has some info on creating menu/toolbar items here ...
http://www.cpearson.com/excel/menus.htm#manual
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub Open_Quotes()
' Open_Quotes Macro
' Macro recorded 1/5/2007 by Tahrah Hunt
' Keyboard Shortcut: Ctrl+Shift+Q
Dim lngRow As Long
Dim rngCell As Excel.Range ' <<< Don't forget this line

Rows("3:5").EntireRow.Hidden = True
Columns("A:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:M").EntireColumn.Hidden = True
Columns("T:T").EntireColumn.Hidden = True
Columns("V:AM").EntireColumn.Hidden = True
Rows("7:2001").Sort Key1:=Range("Q7"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
lngRow = Range("Q2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
'--------
For Each rngCell In Range("U1", Cells(lngRow - 1, "U"))
If rngCell.Value = "order received" Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
'--------
ActiveSheet.PageSetup.PrintArea = Rows("7:" & lngRow - 1).Address '????
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Range("B6").Select
End Sub
'''''''''''''''''''''''''''''''''''''''''''

"tahrah" <[email protected]>
wrote in message
Jim,
Thank you thank you thank you. It works great. I hope you are still
available to help with one more thing.
If I want to hide all rows where column U says "order received", how
would I do that? I would want it to do that before setting the print
area. So the macro should hide all rows where column Q is empty and
all rows where column U says "order received".
Also, isn't there a way to create a button on the worksheet for the
user to click and automatically run this macro?
I appreciate your help. YES! Life Saver!
Regards,
Tahrah

 

[tahrah]

Jim,

Worked like a charm! Thank you very much.

I also figured out how to add the button to the menu bar.

You're wonderful!

Regards,
Tahrah

 

[tahrah]

Jim,

Me Again. LOL I know from your help that

lngRow = Range("Y2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True

will hide any rows where column Y is blank.

Now I need to hide any rows where column "AD" is NOT blank.

I also need to find ONLY the records where column "AK" is between
January 1 and January 31.

Please advise how I would do those last two tasks. After that, I think
I've got everything and now will have three working macros that make
this spreadsheet much more functional and user friendly.

Regards,
Tahrah

 

[Jim Cone]

Me Again,

re: Now I need to hide any rows where column "AD" is NOT blank

You should be able to modify the code posted earlier to do that.
Change the range address as necessary.
Note that the code now checks the length of the cell contents to
determine if it contains anything...

For Each rngCell In Range("AD1", Cells(lngRow - 1, "AD"))
If Len(rngCell.Value) > 0 Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell

I'll let you figure out the date thing.
Jim Cone
San Francisco, USA


"tahrah" <[email protected]>
wrote in message
Jim,
Me Again. LOL I know from your help that
lngRow = Range("Y2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
will hide any rows where column Y is blank.
Now I need to hide any rows where column "AD" is NOT blank.
I also need to find ONLY the records where column "AK" is between
January 1 and January 31.
Please advise how I would do those last two tasks. After that, I think
I've got everything and now will have three working macros that make
this spreadsheet much more functional and user friendly.
Regards,
Tahrah

 

[tahrah]

Hi Jim,

That code worked excellent. Thank you.

Now I've been working on the date range code for about 4 hours and just
can't figure it out. I've tried every combination I can think of and
the last one was this:

For Each rngCell In Range("AK1", Cells(lngRow - 1, "AK"))
If Month(rngCell.Value) <> 1 Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell

Nothing works. It has no problem printing ALL of the rows or NONE of
them, LOL, but not anything in a specific date range. I looked ALL
over this Google Groups for some help and haven't had any luck.

Do you know of another online group where I might be able to get this
figured out?

This is probably so simple but I just can't do it.

Regards,
Tahrah

 

[tahrah]

Okay all I had to do was get frustrated and then it came to me LOL.

Selection.AutoFilter Field:=37, Criteria1:=">=1/1/2007",
Operator:=xlAnd

That worked.

I sure do appreciate your help. U R wonderful!

Regards,
Tahrah

 

[Jim Cone]

Tahrah,
You don't say what the error is or what results you are getting
or any description of the data you entered.
You may need the Excel psychic online group to solve the problem. <g>

My Astrologer said to see what this turns up on one of the date cells...
Sub psychic()
MsgBox TypeName(Range("AK10").Value)
MsgBox VBA.Month(Range("AK10").Value)
End Sub
----------
Jim Cone


"tahrah" <[email protected]>
wrote in message
Hi Jim,
That code worked excellent. Thank you.
Now I've been working on the date range code for about 4 hours and just
can't figure it out. I've tried every combination I can think of and
the last one was this:
For Each rngCell In Range("AK1", Cells(lngRow - 1, "AK"))
If Month(rngCell.Value) <> 1 Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
Nothing works. It has no problem printing ALL of the rows or NONE of
them, LOL, but not anything in a specific date range. I looked ALL
over this Google Groups for some help and haven't had any luck.

Do you know of another online group where I might be able to get this
figured out?
This is probably so simple but I just can't do it.
Regards,
Tahrah

 

[tahrah]

Jim you're so sweet. I needed a laugh after beating my head up against
the wall on this for hours LOL. That was great.

I just posted a new post because now that I finally got "just the
orders for January" to print, I need to sum them up and have the sum
show up just under the last row. The dollar values for the orders are
in column AM.

Here was the final macro for the orders. Just need to figure the sum
part now:

Sub Orders_MTD()
'
' Orders_MTD Macro
' Macro recorded 1/8/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Dim lngRow As Long
Rows("3:5").EntireRow.Hidden = True
Columns("A:E").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:AG").EntireColumn.Hidden = True
lngRow = Range("AK2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
Selection.AutoFilter Field:=37, Criteria1:=">=1/1/2007",
Operator:=xlAnd

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = Rows("6:" & lngRow - 1).Address
'?
With ActiveSheet.PageSetup
.LeftHeader = "PAGE NO. &P"
.CenterHeader = "ORDERS MONTH-TO-DATE"
.RightHeader = "&D, &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=37
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("7:2002").Select
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("B6").Select
End Sub


I do need a psychic. LOL...... To tell me if I'll still have a job if
I don't figure this out. JK

Thank you again for all of your help!

Regards,
Tahrah