Macro to hide rows

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

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

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

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

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

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

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

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

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

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