Now to unhide columns ... and test for upper/lower case

[Brian Belliveau]

I've got two more questions ...

Having the routine to open the rows, I need to apply that to opening the
required columns. I assume that the columns are referred to numerically,
rather than alphabetically? When I run the routine, the first part runs fine
(opening rows), but the second part generates the error message :

Run-time error '1004':
Application-defined or object-defined error
(This occurred with the two routines below combined as one)

Seperating them ( as pasted here ) and running it on its' own gives the
error message:

400

Any help for this ?

Second question ... Can the line "If RowRange.Cells(r, 1).Value = "X" Then"
test for multiple situations, such as upper & lower case, or must it be a
seperate "If" statement?

Once again, I thank you all.

Sub RowUnhide()

'This part opens 2 rows for each employee, in 1st & 2nd weeks

Dim RowRange As Range
Dim r1 As Integer, r2 As Integer, r3 As Integer, r4 As Integer
Set RowRange = Worksheets("Employees").Range("D322")
For r = 3 To 22
If RowRange.Cells(r, 1).Value = "X" Then
r1 = 44 + (2 * r)
r2 = 44 + (2 * r) + 1
r3 = 294 + (2 * r)
r4 = 294 + (2 * r) + 1
Worksheets("PayPeriod_01").Rows(r1 & ":" & r2).Hidden = False
Worksheets("PayPeriod_01").Rows(r3 & ":" & r4).Hidden = False

End If
Next

'second routine placed here, without the "Sub ColUnhide()" part

End Sub
-------------------------------------------------------------------
Sub ColUnhide()
'This part opens 2 columns for each job in progress

Dim ColRange As Range
Dim c1 As Integer, c2 As Integer
Set ColRange = Worksheets("Jobs").Range("F4:F100")
For c = 4 To 100
If ColRange.Cells(c, 1).Value = "X" Then
c1 = -3 + (2 * c)
c2 = -3 + (2 * c) + 1
Worksheets("PayPeriod_01").Columns(c1 & ":" & c2).Hidden = False

End If
Next

End Sub

 

[Don Guillett]

A very cursory look suggests that you don't increase your r after the
procedure.
r=r+1

As to the testing for case you could use

If RowRange.Cells(r, 1).Value = "X" Then

If ucase(RowRange.Cells(r, 1)) = "X" Then

 

[Dave Peterson]

One way is to just use the first column and resize it through the last column:

Option Explicit
Sub ColUnhide()
'This part opens 2 columns for each job in progress

Dim ColRange As Range
Dim c As Long
Dim c1 As Integer
'don't need this line c2 As Integer
Set ColRange = Worksheets("Jobs").Range("F4:F100")
For c = 4 To 100
If ColRange.Cells(c, 1).Value = "" Then
c1 = -3 + (2 * c)
'don't need this line anymore c2 = -3 + (2 * c) + 1
Worksheets("PayPeriod_01").Columns(c1).Resize(, 2).Hidden = False
End If
Next c
End Sub


====
But I think you may have a bug in your first sub, too.

These lines:

Set RowRange = Worksheets("Employees").Range("D322")
For r = 3 To 22
If RowRange.Cells(r, 1).Value = "X" Then

May not be doing what you want.

Put a msgbox line in it like:

Sub test()

Dim RowRange As Range
Dim r As Long
Set RowRange = Worksheets("Employees").Range("D322")
For r = 3 To 22
MsgBox RowRange.Cells(r, 1).Address
Next r
End Sub

and you'll see you're looking at D5 when r = 1. Is that what you meant?

That .cells(r,1) takes its position relative to the top left cell of RowRange.

If you wanted to loop through the rows in the range:

for r = 1 to rowrange.rows.count
msgbox rowrange.cells(r,1).address
next r

Will loop through the first row of the range to the last row in that range.

 

[Brian Belliveau]

Thank You Don

As to the testing for case you could use
If ucase(RowRange.Cells(r, 1)) = "X" Then

 

[Brian Belliveau]

Thank You Dave

I find it odd that columns & rows are treated differently ( or at least
that's how I've ended up)

And, yes, the row opener needed to count from 1 - 19 rather than from 3 -
22.

( And now I try to get the one code to work for a number of sheets ! )

Brian

One way is to just use the first column and resize it through the last column:

Option Explicit
Sub ColUnhide()
'This part opens 2 columns for each job in progress

Dim ColRange As Range
Dim c As Long
Dim c1 As Integer
'don't need this line c2 As Integer
Set ColRange = Worksheets("Jobs").Range("F4:F100")
For c = 4 To 100
If ColRange.Cells(c, 1).Value = "" Then
c1 = -3 + (2 * c)
'don't need this line anymore c2 = -3 + (2 * c) + 1
Worksheets("PayPeriod_01").Columns(c1).Resize(, 2).Hidden = False
End If
Next c
End Sub


====
But I think you may have a bug in your first sub, too.

These lines:

Set RowRange = Worksheets("Employees").Range("D322")
For r = 3 To 22
If RowRange.Cells(r, 1).Value = "X" Then

May not be doing what you want.

Put a msgbox line in it like:

Sub test()

Dim RowRange As Range
Dim r As Long
Set RowRange = Worksheets("Employees").Range("D322")
For r = 3 To 22
MsgBox RowRange.Cells(r, 1).Address
Next r
End Sub

and you'll see you're looking at D5 when r = 1. Is that what you meant?

That .cells(r,1) takes its position relative to the top left cell of RowRange.

If you wanted to loop through the rows in the range:

for r = 1 to rowrange.rows.count
msgbox rowrange.cells(r,1).address
next r

Will loop through the first row of the range to the last row in that

range.