If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Macro to hide rows
Dave, My first question, where do I learn to write script like this?
Second, When I try the first row limiting script, I get a script error highlighting the FirstRow = 14 as field not defined. With the intersect script, there is a syntax error in the string, don't know enough to find it. Thanks again, Markstro Dave Peterson wrote in message ... One way to limit the rows is to find the top row and the bottom row and use them in your range: Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 14 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")) .Cells.SpecialCells(xlCellTypeConstants) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub Another way is to use the Intersect method to limit the rows: Change: Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) to: Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) markstro wrote: Thanks Dave, it works fine, one more problem, how can I set it to only hide column P from row 14 thru the last row. The heading information from row 1 thru 14 is hidden as the script is set now. I tried to designate P14:P251 in the set row command and it wouldn't run. Thanks again, markstro Dave Peterson wrote in message ... This line looks at all of column A of worksheets("sheet1"): Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) It's like selecting column A, then hitting edit|goto|special then selecting the Constants option button. My first guess is the subscript out of range error occurred because I looked for a worksheet named Sheet1. If your worksheet is named something different, change the name. If you want to run it against the activesheet (no matter the name), change: Set wks = Worksheets("sheet1") to Set wks = ActiveSheet If that wasn't the problem, post the code that blows up and the indicate which one is the culprit. Good luck, markstro wrote: Dave, it worked for a few tests and then I started to get a "script out of range" error. All I did was put a shortcut to the macro in options then try the macro in the actual spreadsheet I need to use it in. I received the error immediately in the actual spreadsheet and then went back to the test sheet and it would not run there either, same error message. How is the range for the entire spreadsheet designated, I do not recognize the text in the script. Dave Peterson wrote in message ... I think I'd consider trying to put all the data on one row if possible. Then you could use data|filter|autofilter to hide the stuff you don't want to see. But if that's not possible, this might work to hide (not delete!) the rows that have an employee's gross pay of 0. (Is that enough to check?) Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub It also assumes that the stuff in column A is constants (not formulas). markstro wrote: Yes I did, but I may only enter data for say 5 - 10 employees in a spreadsheet of around 80 employees, after I unhide all rows to enter data, I would like to hide only the employees that had no data entered. example follows: column A column P a1 empty row a2 name gross pay a3 address benefit pay a4 city, state, zip taxable gross a5 empty row a6 name gross pay a7 address benefit pay a8 city, state, zip taxable gross a9 empty row there are more columns in the spreadsheet, however, these should give you the idea, if I do not enter data that produces a figure higher than zero, I would like to delete the three rows plus the blank row under each employee. Currently my rows start at a blank row at 15 and end at 251 with another blank row. I hope this helps, thanks for your time. Markstro "Don Guillett" wrote in message ... I thought you asked for FOUR rows per person? -- Don Guillett SalesAid Software "markstro" wrote in message om... Thanks Don, I tried this with changes: range b1:b4 to p15255 (the amount or rows in my spreadsheet and the column setting the range) then rows changed from 1:4 to 15:255. It didn't work, what did I do wrong? "Don Guillett" wrote in message ... something like this? Sub countem() If Application.CountA(Range("b1:b4")) 1 Then Rows("1:4").Hidden = True End Sub -- Don Guillett SalesAid Software "markstro" wrote in message om... I have a certified payroll spreadsheet in which I want to display only the employees that have pay data entered that week. I often unhide all rows, enter data, then have to selectively hide the rows of employees that had no data entered. I would like to do a macro that will hide only the rows (3 per employee + one blank row)that have no pay data entered. I cannot find any examples quite like this or come up with the true/false argument common to all 4 rows to make the macro work. Markstro |
#12
|
|||
|
|||
Macro to hide rows
Dave, excuse the second message, I found the problem with the first
script, I forgot to put in Dim FirstRow & LastRow as Long. Now the (x1Up) in the LastRow under With wks comes up with a variable not defined error. Thanks again, Mark Dave Peterson wrote in message ... One way to limit the rows is to find the top row and the bottom row and use them in your range: Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 14 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")) .Cells.SpecialCells(xlCellTypeConstants) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub Another way is to use the Intersect method to limit the rows: Change: Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) to: Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) markstro wrote: Thanks Dave, it works fine, one more problem, how can I set it to only hide column P from row 14 thru the last row. The heading information from row 1 thru 14 is hidden as the script is set now. I tried to designate P14:P251 in the set row command and it wouldn't run. Thanks again, markstro Dave Peterson wrote in message ... This line looks at all of column A of worksheets("sheet1"): Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) It's like selecting column A, then hitting edit|goto|special then selecting the Constants option button. My first guess is the subscript out of range error occurred because I looked for a worksheet named Sheet1. If your worksheet is named something different, change the name. If you want to run it against the activesheet (no matter the name), change: Set wks = Worksheets("sheet1") to Set wks = ActiveSheet If that wasn't the problem, post the code that blows up and the indicate which one is the culprit. Good luck, markstro wrote: Dave, it worked for a few tests and then I started to get a "script out of range" error. All I did was put a shortcut to the macro in options then try the macro in the actual spreadsheet I need to use it in. I received the error immediately in the actual spreadsheet and then went back to the test sheet and it would not run there either, same error message. How is the range for the entire spreadsheet designated, I do not recognize the text in the script. Dave Peterson wrote in message ... I think I'd consider trying to put all the data on one row if possible. Then you could use data|filter|autofilter to hide the stuff you don't want to see. But if that's not possible, this might work to hide (not delete!) the rows that have an employee's gross pay of 0. (Is that enough to check?) Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub It also assumes that the stuff in column A is constants (not formulas). markstro wrote: Yes I did, but I may only enter data for say 5 - 10 employees in a spreadsheet of around 80 employees, after I unhide all rows to enter data, I would like to hide only the employees that had no data entered. example follows: column A column P a1 empty row a2 name gross pay a3 address benefit pay a4 city, state, zip taxable gross a5 empty row a6 name gross pay a7 address benefit pay a8 city, state, zip taxable gross a9 empty row there are more columns in the spreadsheet, however, these should give you the idea, if I do not enter data that produces a figure higher than zero, I would like to delete the three rows plus the blank row under each employee. Currently my rows start at a blank row at 15 and end at 251 with another blank row. I hope this helps, thanks for your time. Markstro "Don Guillett" wrote in message ... I thought you asked for FOUR rows per person? -- Don Guillett SalesAid Software "markstro" wrote in message om... Thanks Don, I tried this with changes: range b1:b4 to p15255 (the amount or rows in my spreadsheet and the column setting the range) then rows changed from 1:4 to 15:255. It didn't work, what did I do wrong? "Don Guillett" wrote in message ... something like this? Sub countem() If Application.CountA(Range("b1:b4")) 1 Then Rows("1:4").Hidden = True End Sub -- Don Guillett SalesAid Software "markstro" wrote in message om... I have a certified payroll spreadsheet in which I want to display only the employees that have pay data entered that week. I often unhide all rows, enter data, then have to selectively hide the rows of employees that had no data entered. I would like to do a macro that will hide only the rows (3 per employee + one blank row)that have no pay data entered. I cannot find any examples quite like this or come up with the true/false argument common to all 4 rows to make the macro work. Markstro |
#13
|
|||
|
|||
Macro to hide rows
Instead of retyping the message into the VBE, you may want to copy and paste.
Everyonce in awhile, you'll have to fix some linewraps, but you won't induce typos. That "xlup" is really x(ell)up--not x(one)up. About the only thing that I can see with this line: Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) is that there might not be constants in column A. (Are they all formulas?). You could add a bit of protection with this type thing: Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = Nothing On Error Resume Next Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No constants--Quitting!" Exit Sub End If For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub And if you lurk around the newsgroups, you can get awfully dangerous! You can read other people's real life posts and see the responses. If someone is off-base, there's usually a correction (or three) soon to arrive. Even if you don't post a possible solution, just trying and comparing your code with others is a good way to learn. If you're interested in books--nice to read in front of the tv: For excel books, Debra Dalgleish has a big list of books at: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. John Green (and others) is nice, too. See if you can find them in your local bookstore and you can choose what one you like best. And there are lots of resources on the web. You may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm markstro wrote: Dave, excuse the second message, I found the problem with the first script, I forgot to put in Dim FirstRow & LastRow as Long. Now the (x1Up) in the LastRow under With wks comes up with a variable not defined error. Thanks again, Mark Dave Peterson wrote in message ... One way to limit the rows is to find the top row and the bottom row and use them in your range: Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 14 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")) .Cells.SpecialCells(xlCellTypeConstants) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub Another way is to use the Intersect method to limit the rows: Change: Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) to: Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) markstro wrote: Thanks Dave, it works fine, one more problem, how can I set it to only hide column P from row 14 thru the last row. The heading information from row 1 thru 14 is hidden as the script is set now. I tried to designate P14:P251 in the set row command and it wouldn't run. Thanks again, markstro Dave Peterson wrote in message ... This line looks at all of column A of worksheets("sheet1"): Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) It's like selecting column A, then hitting edit|goto|special then selecting the Constants option button. My first guess is the subscript out of range error occurred because I looked for a worksheet named Sheet1. If your worksheet is named something different, change the name. If you want to run it against the activesheet (no matter the name), change: Set wks = Worksheets("sheet1") to Set wks = ActiveSheet If that wasn't the problem, post the code that blows up and the indicate which one is the culprit. Good luck, markstro wrote: Dave, it worked for a few tests and then I started to get a "script out of range" error. All I did was put a shortcut to the macro in options then try the macro in the actual spreadsheet I need to use it in. I received the error immediately in the actual spreadsheet and then went back to the test sheet and it would not run there either, same error message. How is the range for the entire spreadsheet designated, I do not recognize the text in the script. Dave Peterson wrote in message ... I think I'd consider trying to put all the data on one row if possible. Then you could use data|filter|autofilter to hide the stuff you don't want to see. But if that's not possible, this might work to hide (not delete!) the rows that have an employee's gross pay of 0. (Is that enough to check?) Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub It also assumes that the stuff in column A is constants (not formulas). markstro wrote: Yes I did, but I may only enter data for say 5 - 10 employees in a spreadsheet of around 80 employees, after I unhide all rows to enter data, I would like to hide only the employees that had no data entered. example follows: column A column P a1 empty row a2 name gross pay a3 address benefit pay a4 city, state, zip taxable gross a5 empty row a6 name gross pay a7 address benefit pay a8 city, state, zip taxable gross a9 empty row there are more columns in the spreadsheet, however, these should give you the idea, if I do not enter data that produces a figure higher than zero, I would like to delete the three rows plus the blank row under each employee. Currently my rows start at a blank row at 15 and end at 251 with another blank row. I hope this helps, thanks for your time. Markstro "Don Guillett" wrote in message ... I thought you asked for FOUR rows per person? -- Don Guillett SalesAid Software "markstro" wrote in message om... Thanks Don, I tried this with changes: range b1:b4 to p15255 (the amount or rows in my spreadsheet and the column setting the range) then rows changed from 1:4 to 15:255. It didn't work, what did I do wrong? "Don Guillett" wrote in message ... something like this? Sub countem() If Application.CountA(Range("b1:b4")) 1 Then Rows("1:4").Hidden = True End Sub -- Don Guillett SalesAid Software "markstro" wrote in message om... I have a certified payroll spreadsheet in which I want to display only the employees that have pay data entered that week. I often unhide all rows, enter data, then have to selectively hide the rows of employees that had no data entered. I would like to do a macro that will hide only the rows (3 per employee + one blank row)that have no pay data entered. I cannot find any examples quite like this or come up with the true/false argument common to all 4 rows to make the macro work. Markstro -- Dave Peterson |
#14
|
|||
|
|||
Macro to hide rows
Dave, thanks a lot for the help, the macro works fine now.
You don't know how much time it will save every week for me. Mark Dave Peterson wrote in message ... Instead of retyping the message into the VBE, you may want to copy and paste. Everyonce in awhile, you'll have to fix some linewraps, but you won't induce typos. That "xlup" is really x(ell)up--not x(one)up. About the only thing that I can see with this line: Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) is that there might not be constants in column A. (Are they all formulas?). You could add a bit of protection with this type thing: Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = Nothing On Error Resume Next Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No constants--Quitting!" Exit Sub End If For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub And if you lurk around the newsgroups, you can get awfully dangerous! You can read other people's real life posts and see the responses. If someone is off-base, there's usually a correction (or three) soon to arrive. Even if you don't post a possible solution, just trying and comparing your code with others is a good way to learn. If you're interested in books--nice to read in front of the tv: For excel books, Debra Dalgleish has a big list of books at: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. John Green (and others) is nice, too. See if you can find them in your local bookstore and you can choose what one you like best. And there are lots of resources on the web. You may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm markstro wrote: Dave, excuse the second message, I found the problem with the first script, I forgot to put in Dim FirstRow & LastRow as Long. Now the (x1Up) in the LastRow under With wks comes up with a variable not defined error. Thanks again, Mark Dave Peterson wrote in message ... One way to limit the rows is to find the top row and the bottom row and use them in your range: Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 14 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")) .Cells.SpecialCells(xlCellTypeConstants) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub Another way is to use the Intersect method to limit the rows: Change: Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) to: Set myRng = Intersect(.Range("14:65536"), _ .Range("a:a")).Cells.SpecialCells(xlCellTypeConsta nts) markstro wrote: Thanks Dave, it works fine, one more problem, how can I set it to only hide column P from row 14 thru the last row. The heading information from row 1 thru 14 is hidden as the script is set now. I tried to designate P14:P251 in the set row command and it wouldn't run. Thanks again, markstro Dave Peterson wrote in message ... This line looks at all of column A of worksheets("sheet1"): Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) It's like selecting column A, then hitting edit|goto|special then selecting the Constants option button. My first guess is the subscript out of range error occurred because I looked for a worksheet named Sheet1. If your worksheet is named something different, change the name. If you want to run it against the activesheet (no matter the name), change: Set wks = Worksheets("sheet1") to Set wks = ActiveSheet If that wasn't the problem, post the code that blows up and the indicate which one is the culprit. Good luck, markstro wrote: Dave, it worked for a few tests and then I started to get a "script out of range" error. All I did was put a shortcut to the macro in options then try the macro in the actual spreadsheet I need to use it in. I received the error immediately in the actual spreadsheet and then went back to the test sheet and it would not run there either, same error message. How is the range for the entire spreadsheet designated, I do not recognize the text in the script. Dave Peterson wrote in message ... I think I'd consider trying to put all the data on one row if possible. Then you could use data|filter|autofilter to hide the stuff you don't want to see. But if that's not possible, this might work to hide (not delete!) the rows that have an employee's gross pay of 0. (Is that enough to check?) Option Explicit Sub testme01() Dim myRng As Range Dim myArea As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) For Each myArea In myRng.Areas If .Cells(myArea.Row, "P").Value = 0 Then myArea.Resize(4, 1).EntireRow.Hidden = True End If Next myArea End With End Sub It also assumes that the stuff in column A is constants (not formulas). markstro wrote: Yes I did, but I may only enter data for say 5 - 10 employees in a spreadsheet of around 80 employees, after I unhide all rows to enter data, I would like to hide only the employees that had no data entered. example follows: column A column P a1 empty row a2 name gross pay a3 address benefit pay a4 city, state, zip taxable gross a5 empty row a6 name gross pay a7 address benefit pay a8 city, state, zip taxable gross a9 empty row there are more columns in the spreadsheet, however, these should give you the idea, if I do not enter data that produces a figure higher than zero, I would like to delete the three rows plus the blank row under each employee. Currently my rows start at a blank row at 15 and end at 251 with another blank row. I hope this helps, thanks for your time. Markstro "Don Guillett" wrote in message ... I thought you asked for FOUR rows per person? -- Don Guillett SalesAid Software "markstro" wrote in message om... Thanks Don, I tried this with changes: range b1:b4 to p15255 (the amount or rows in my spreadsheet and the column setting the range) then rows changed from 1:4 to 15:255. It didn't work, what did I do wrong? "Don Guillett" wrote in message ... something like this? Sub countem() If Application.CountA(Range("b1:b4")) 1 Then Rows("1:4").Hidden = True End Sub -- Don Guillett SalesAid Software "markstro" wrote in message om... I have a certified payroll spreadsheet in which I want to display only the employees that have pay data entered that week. I often unhide all rows, enter data, then have to selectively hide the rows of employees that had no data entered. I would like to do a macro that will hide only the rows (3 per employee + one blank row)that have no pay data entered. I cannot find any examples quite like this or come up with the true/false argument common to all 4 rows to make the macro work. Markstro |
#15
|
|||
|
|||
Macro to hide rows
|
|
Thread Tools | |
Display Modes | |
|
|