Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
I'm looking for a macro that checks the title of columns and hides
particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
Try something like this:
Sub HideCols() Dim arr Dim i As Long Dim n As Long 'setting up array holding column names to hide With Sheets("Sheet2") arr = .Range(.Cells(1), .Cells(10, 1)) End With For i = 20 To 2 step - 1 For n = 1 To UBound(arr) If Cells(i) = arr(n, 1) Then Columns(i).EntireColumn.Hidden = True Exit For End If Next Next End Sub Your list of names to hide will be in Sheet2 in cells A1 to A10 in this example. RBS wrote in message oups.com... I'm looking for a macro that checks the title of columns and hides particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
I have an addin that I use to hide columns, which has a default setting
that can be used. I can email it to you if you would like. Charles wrote: I'm looking for a macro that checks the title of columns and hides particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
Thanks, sorry for my lameness, but could you show how it would look if
all of column A on sheet 2 contained the values? Thanks! RB Smissaert wrote: Try something like this: Sub HideCols() Dim arr Dim i As Long Dim n As Long 'setting up array holding column names to hide With Sheets("Sheet2") arr = .Range(.Cells(1), .Cells(10, 1)) End With For i = 20 To 2 step - 1 For n = 1 To UBound(arr) If Cells(i) = arr(n, 1) Then Columns(i).EntireColumn.Hidden = True Exit For End If Next Next End Sub Your list of names to hide will be in Sheet2 in cells A1 to A10 in this example. RBS wrote in message oups.com... I'm looking for a macro that checks the title of columns and hides particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
Change this:
arr = .Range(.Cells(1), .Cells(10, 1)) to this: arr = .Range(.Cells(1), .Cells(65536, 1)) RBS wrote in message ups.com... Thanks, sorry for my lameness, but could you show how it would look if all of column A on sheet 2 contained the values? Thanks! RB Smissaert wrote: Try something like this: Sub HideCols() Dim arr Dim i As Long Dim n As Long 'setting up array holding column names to hide With Sheets("Sheet2") arr = .Range(.Cells(1), .Cells(10, 1)) End With For i = 20 To 2 step - 1 For n = 1 To UBound(arr) If Cells(i) = arr(n, 1) Then Columns(i).EntireColumn.Hidden = True Exit For End If Next Next End Sub Your list of names to hide will be in Sheet2 in cells A1 to A10 in this example. RBS wrote in message oups.com... I'm looking for a macro that checks the title of columns and hides particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
Hi,
How would you make this work to hide columns that just contain certain pieces of text. For example, hide all columns that contain the phrase "apple" so column with header "blue apple" would be hidden? thanks very much... "RB Smissaert" wrote: Change this: arr = .Range(.Cells(1), .Cells(10, 1)) to this: arr = .Range(.Cells(1), .Cells(65536, 1)) RBS wrote in message ups.com... Thanks, sorry for my lameness, but could you show how it would look if all of column A on sheet 2 contained the values? Thanks! RB Smissaert wrote: Try something like this: Sub HideCols() Dim arr Dim i As Long Dim n As Long 'setting up array holding column names to hide With Sheets("Sheet2") arr = .Range(.Cells(1), .Cells(10, 1)) End With For i = 20 To 2 step - 1 For n = 1 To UBound(arr) If Cells(i) = arr(n, 1) Then Columns(i).EntireColumn.Hidden = True Exit For End If Next Next End Sub Your list of names to hide will be in Sheet2 in cells A1 to A10 in this example. RBS wrote in message oups.com... I'm looking for a macro that checks the title of columns and hides particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide column based on header name
Hi,
How would you make this work to hide columns that just contain certain pieces of text. For example, hide all columns that contain the phrase "apple" so column with header "blue apple" would be hidden? thanks very much... "RB Smissaert" wrote: Change this: arr = .Range(.Cells(1), .Cells(10, 1)) to this: arr = .Range(.Cells(1), .Cells(65536, 1)) RBS wrote in message ups.com... Thanks, sorry for my lameness, but could you show how it would look if all of column A on sheet 2 contained the values? Thanks! RB Smissaert wrote: Try something like this: Sub HideCols() Dim arr Dim i As Long Dim n As Long 'setting up array holding column names to hide With Sheets("Sheet2") arr = .Range(.Cells(1), .Cells(10, 1)) End With For i = 20 To 2 step - 1 For n = 1 To UBound(arr) If Cells(i) = arr(n, 1) Then Columns(i).EntireColumn.Hidden = True Exit For End If Next Next End Sub Your list of names to hide will be in Sheet2 in cells A1 to A10 in this example. RBS wrote in message oups.com... I'm looking for a macro that checks the title of columns and hides particular ones. For example, using the bwlo sample sheet, a macro that would hide the Age and Location columns automatically: Name Age Sex Location John 1 M Home Steve 4 F Work As a further capability, I was thinking of creating a spreadsheet with a listing of all of the columns that I would like hidden, and then reference that in the macro. So, if the header equals a value on the column hiding list, the macro would hide that column in my actual spreadsheet. Any thoughts? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
Macro to rearrange/add columns based on column header? | Excel Programming | |||
Hide column depending on its header | Excel Programming | |||
hide columns based on header macro | Excel Programming | |||
macro to hide column based on header | Excel Programming |