Macro to hide rows with a certain cell empty then set print area and print
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 |
Macro to hide rows with a certain cell empty then set print area and print
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" 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- |
Macro to hide rows with a certain cell empty then set print area and print
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 wrote: 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" 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- |
Macro to hide rows with a certain cell empty then set print area and print
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 "tahrah" wrote in message ps.com... 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 wrote: 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" 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- |
Macro to hide rows with a certain cell empty then set print area and print
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 wrote: 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 "tahrah" wrote in message ps.com... 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 wrote: 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" 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- |
Macro to hide rows with a certain cell empty then set print area and print
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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
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 Jim Cone wrote: 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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
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 wrote: 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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
Me Again,
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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
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 Jim Cone wrote: Me Again, 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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
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 tahrah wrote: 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 Jim Cone wrote: Me Again, 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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
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" 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 |
Macro to hide rows with a certain cell empty then set print area and print
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 Jim Cone wrote: 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" 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 |
All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com