Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 -----Original Message----- This checks the value for all cells in the range A1 through J10 and if any of the values are equal to XXX it hides the column. Just change the value of the Range and you can also replace Worksheets(1) with Worksheets ("Sheet_Name").... Hope this is what you are looking for. Sub HideInactive() For Each c In Worksheets(1).Range("A1:J10") If c.Value = "XXX" Then c.EntireColumn.Select Selection.EntireColumn.Hidden = True End If Next c End Sub -----Original Message----- 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 . . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 Todd wrote in message ... 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 -----Original Message----- This checks the value for all cells in the range A1 through J10 and if any of the values are equal to XXX it hides the column. Just change the value of the Range and you can also replace Worksheets(1) with Worksheets ("Sheet_Name").... Hope this is what you are looking for. Sub HideInactive() For Each c In Worksheets(1).Range("A1:J10") If c.Value = "XXX" Then c.EntireColumn.Select Selection.EntireColumn.Hidden = True End If Next c End Sub -----Original Message----- 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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 Todd wrote in message ... 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 -----Original Message----- This checks the value for all cells in the range A1 through J10 and if any of the values are equal to XXX it hides the column. Just change the value of the Range and you can also replace Worksheets(1) with Worksheets ("Sheet_Name").... Hope this is what you are looking for. Sub HideInactive() For Each c In Worksheets(1).Range("A1:J10") If c.Value = "XXX" Then c.EntireColumn.Select Selection.EntireColumn.Hidden = True End If Next c End Sub -----Original Message----- 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 . . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 -----Original Message----- 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 Todd wrote in message ... 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 -----Original Message----- This checks the value for all cells in the range A1 through J10 and if any of the values are equal to XXX it hides the column. Just change the value of the Range and you can also replace Worksheets(1) with Worksheets ("Sheet_Name").... Hope this is what you are looking for. Sub HideInactive() For Each c In Worksheets(1).Range("A1:J10") If c.Value = "XXX" Then c.EntireColumn.Select Selection.EntireColumn.Hidden = True End If Next c End Sub -----Original Message----- 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 . . . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 -- Regards, Tom Ogilvy "Todd" wrote in message ... 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 -----Original Message----- 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 Todd wrote in message ... 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 -----Original Message----- This checks the value for all cells in the range A1 through J10 and if any of the values are equal to XXX it hides the column. Just change the value of the Range and you can also replace Worksheets(1) with Worksheets ("Sheet_Name").... Hope this is what you are looking for. Sub HideInactive() For Each c In Worksheets(1).Range("A1:J10") If c.Value = "XXX" Then c.EntireColumn.Select Selection.EntireColumn.Hidden = True End If Next c End Sub -----Original Message----- 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 . . . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide column based on header
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 -----Original Message----- 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 -- Regards, Tom Ogilvy "Todd" wrote in message ... 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 -----Original Message----- 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 Todd wrote in message ... 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 -----Original Message----- This checks the value for all cells in the range A1 through J10 and if any of the values are equal to XXX it hides the column. Just change the value of the Range and you can also replace Worksheets(1) with Worksheets ("Sheet_Name").... Hope this is what you are looking for. Sub HideInactive() For Each c In Worksheets(1).Range("A1:J10") If c.Value = "XXX" Then c.EntireColumn.Select Selection.EntireColumn.Hidden = True End If Next c End Sub -----Original Message----- 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 . . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return column header based on last value in row | Excel Worksheet Functions | |||
Returning Column Header based on Row and Value | New Users to Excel | |||
Hide column header or first field in a list | Excel Discussion (Misc queries) | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
Hide column or row based on a cell in the column or row? | Excel Discussion (Misc queries) |