Macro to hide rows

[markstro]

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

 

[Don Guillett]

something like this?

Sub countem()
If Application.CountA(Range("b1:b4")) < 1 Then Rows("1:4").Hidden = True
End Sub

 

[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]

I thought you asked for FOUR rows per person?

 

[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

 

[Dave Peterson]

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(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

It also assumes that the stuff in column A is constants (not formulas).

 

[markstro]

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]

This line looks at all of column A of worksheets("sheet1"):
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstants)

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]

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 P14251 in the set row command and it wouldn't run.
Thanks again,
markstro

 

[Dave Peterson]

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(xlCellTypeConstants)
to:
Set myRng = Intersect(.Range("14:65536"), _
.Range("a:a")).Cells.SpecialCells(xlCellTypeConstants)

 

[markstro]

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

 

[markstro]

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]

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(xlCellTypeConstants)

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(xlCellTypeConstants)
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]

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]

Woohoo. (more coffee breaks???)

Glad it worked.

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

<<snipped>>