Macro to unhide selected columns & rows

  • Thread starter Thread starter Brian Belliveau
  • Start date Start date
B

Brian Belliveau

I'd like to develop a 'daily log', using one sheet for each of my 2 week pay
periods, which allows me to cross reference the particular jobs we are
working on during that time with the employees of the time. The idea being
to have a cell opened for each employees start/finish times on each job we
have on the go.

As I see it, I need to use a pair of columns for each employee, per day
(start / finish time entry) , allowing for, say 20 employees (many labourors
coming and going during the season) [ (1st 2 columns for job info) + (6 days
X 20 employees X 2 columns) + (6 days X 2 columns for summary info) ]= 254
columns (pretty tight quarters!) and a pair of rows for each 'job on the go'
(one row for the start/finish times, row below for summary of time) [ (100
jobs X 2 rows each) ... well ... it doesn't require a full analysis 'cause
there are plenty of rows, but the first week will be stacked over the second
week to keep control of the column requirements ]

Can anyone help me create a routine which will test a first series of cells
(=Jobs!C20:C100) for 'anything in it' (a checkmark would be nice, but an "X"
will do) and then 'unhide' a corresponding pair of double rows
(=PayPeriod_01!50:51, & =PayPeriod_01!1050:1051 for the first job, first and
second weeks. respectively), and then test a second series of cells
(=Employees!D3:D22, for PayPeriod_01) for similar input, causing a pair of
columns(=PayPeriod_01!C:D) to unhide.

Anyone able to help me ?

Thanks

Brian Belliveau
Thunder Bay, Ont
 
Hopefully this will start you off :-
'==============================================
Sub test()
Dim MyRange As Range
Set MyRange = Worksheets("Jobs").Range("C20:C100")
For c = 20 To 100
If MyRange.Cells(c, 1).Value = "X" Then
Worksheets("PayPeriod_01").Rows("50:51").Hidden = False
Worksheets("PayPeriod_01").Rows("1050:1051").Hidden
False
End If
Next
End Sub
'============================================
 
Thanks BrianB ... ( feels like I'm thanking myself ! )

I can see just how to apply this to my horizontal and vertical requirements,
but ...

It appears that the lines -

Worksheets("PayPeriod_01").Rows("50:51").Hidden = False
Worksheets("PayPeriod_01").Rows("1050:1051").Hidden = False

need to increment in (double) step with "For c = 20 To 100"

( I don't know how to introduce a math function into the row selection )

If you ( or someone else ) could offer the coding to cause this, I am sure I
can use this routine for both my columns and rows.

Thanks to all

Brian Belliveau
 
Back
Top