parameter queries in macros

[lindasf]

Hello, The following macro works great!

Sub hidecolumns()
Columns("d:fz").Hidden = False
Dim sPrompt As String
sPrompt = "Enter 1 for Initial DHS Recommendation" & vbNewLine & "Ente
2 for Project Recommendation" & vbNewLine & "Enter 3 for Final DH
Recommendation" & vbNewLine & "Enter 4 for Project/DHS Matches"
vbNewLine & "Enter 5 for DHS VDAT Modules"
x = InputBox(sPrompt)
For Each c In Range("d1:fz1")
If Right(c, 1) <> x Then c.EntireColumn.Hidden = True
Next
End Sub

However, I would like to change this macro or create a new one a
follows:

If you enter "5" in the parameter query, I would like it to displa
only the columns that:

* contain a 5 in the header (as it is now) AND
* have a D in at least one cell of that column

I don't have to put this logic in this particular macro. I can put i
in a new/separate macro if that is less complicated.

Thx. much! (file attached)

lindas

Attachment filename: calwin food stamps training analysis-dhs vdat-demo.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=62450

 

[Don Guillett]

This should do it

Sub find5andD()
hideall 'call your macro to hide all columns
For Each col In Columns("d:fz")
If Right(Cells(1, col.Column), 1) = 5 And _
Application.CountIf(col, "D") > 0 Then col.Hidden = False
Next
End Sub

 

[MSP77079]

Try this:

Sub hidecolumns()
Columns("d:fz").Hidden = False
Dim sPrompt As String
sPrompt = "Enter 1 for Initial DHS Recommendation" & vbNewLine & "Ente
2 for Project Recommendation" & vbNewLine & "Enter 3 for Final DH
Recommendation" & vbNewLine & "Enter 4 for Project/DHS Matches"
vbNewLine & "Enter 5 for DHS VDAT Modules"
x = InputBox(sPrompt)
Set myRange = Range("A1").CurrentRegion
LastRow = myRange.Cells(myRange.Cells.Count).Row
For Each c In Range("d1:fz1")
If Right(c, 1) <> x Then
c.EntireColumn.Hidden = True
Else:
Hide = True
j = c.Column
For i = 2 To LastRow
If Cells(i, j) = "D" Then Hide = False
Next i
If Hide Then c.EntireColumn.Hidden = True
End If
Next
End Su

 

[lindasf]

Thx much guys

 

[Don Guillett]

glad to help