macro to hide column based on header

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I've been using auto filter to hide unwanted columns and
rows. I tried recording that as a macro but it is doesn't
work to well. I need a better macro that would hide
columns where the header is "Inactive" or the row
contains "XXX". Can anyone help?


Todd
 
Thanks for the help. I used what you gave me and came up
with this. But its not quite right. the Inactive macro
hides columns A:J No matter what?

The XXX macro doesn't work at all yet.
I am learning but slow! Can you see where I went wrong?


Todd


Sub HideInactive()

For Each c In Worksheets(1).Range("A1:J10")
If c.Value = Inactive Then
c.EntireColumn.Select
Selection.EntireColumn.Hidden = True

End If
Next c

End Sub

Sub HideXXX()
For Each c In Selection
If c.Value = "x*" Then c.EntireRow.Select
Selection.EntireRow.Hidden = True

Next c

End Sub
 
Searching for the string Inactive in the top row?

Sub HideInactive()

For Each c In Worksheets(1).Range("A1:J10")
InActive = "INACTIVE"
If Ucase(c.Value) = Inactive Then
c.EntireColumn.Hidden = True
End If
Next c

End Sub

As you had it written, inactive was a variable with no value. All empty
cells in the first row would have their column hidden. Use Ucase to make it
case insensitive.

Sub HideXXX()
For Each c In Selection
If Left(lcase(c.Value),1) = "x" Then
c.EntireRow.Hidden = True
Next c

End Sub

Equality doesn't work with a wildcard. If you want to check the if the
first letter is x, then extract the first letter with the LEFT command and
compare it. Use lcase to make the check case insensitive.
 
Thanks!!!!

Todd
-----Original Message-----

Searching for the string Inactive in the top row?

Sub HideInactive()

For Each c In Worksheets(1).Range("A1:J10")
InActive = "INACTIVE"
If Ucase(c.Value) = Inactive Then
c.EntireColumn.Hidden = True
End If
Next c

End Sub

As you had it written, inactive was a variable with no value. All empty
cells in the first row would have their column hidden. Use Ucase to make it
case insensitive.

Sub HideXXX()
For Each c In Selection
If Left(lcase(c.Value),1) = "x" Then
c.EntireRow.Hidden = True
Next c

End Sub

Equality doesn't work with a wildcard. If you want to check the if the
first letter is x, then extract the first letter with the LEFT command and
compare it. Use lcase to make the check case insensitive.

--
Regards,
Tom Ogilvy







.
Click to expand...
 
Sorry for being so dumb,

I have the macro working but it only hides the first
column where "Inactive" occurs. I changed it a little to
make it search cells I select. All the cells should be in
the 10th row but this way I know for sure. I have been
looking through macro's listed here but I don't see why I
don't hide more than the first "Inactive" it hits.

Thanks again,


Todd


Sub HideInactive()

For Each c In Selection
Inactive = "INACTIVE"
If UCase(c.Value) = Inactive Then
c.EntireColumn.Hidden = True
End If
Next c

End Sub
 
Maybe there is a space in the cell along with Inactive. From lookin at the
code, it should hide any column containing the single word Inactive. Here
is a fix to remove spaces on each end for purposes of the comparison.

Sub HideInactive()

For Each c In Selection
Inactive = "INACTIVE"
If UCase(Trim(c.Value)) = Inactive Then
c.EntireColumn.Hidden = True
End If
Next c

End Sub
 
Thank you very much, The other code works in my test
sheets but not in the actual workbook. I had tried
clearing those cells and retyping the info. Something
must be there that can't be seen, so you must be right.

Thanks again, you are a great help.



Todd
 
Back
Top