Settings to apply to automatically hide columns

Copper Contributor

Hello,

 

I often have to export data from ministerial software and there are a lot of unnecessary columns. I cannot select the columns to export, the software only allows me to export all the columns. Is it possible to apply settings that allow me to automatically hide unnecessary columns?

 

THANKS

2 Replies

@DaphneC 

Here's another method using Excel's "Custom Views" feature:

  1. Define a Custom View:
    • Arrange your sheet by hiding the unnecessary columns as you want.
    • Go to the "View" tab on the ribbon.
    • Click on "Custom Views" in the "Workbook Views" group.
    • Click on "Add" to create a new custom view.
    • Give your view a name (e.g., "Export View") and click "OK."
  2. Switch Between Views:
    • Whenever you need to hide unnecessary columns, go back to the "View" tab.
    • Click on "Custom Views" and select the custom view you created ("Export View").
    • This will restore the hidden columns to the state you saved in your custom view.

This method allows you to easily switch between different column arrangements without using filtering or grouping. It's particularly useful when you have specific column configurations you frequently use.

Here some more couple of methods you can try:

  1. Grouping Columns:
    • Select the columns you want to group. You can do this by holding down the Ctrl key and clicking on the column headers.
    • Right-click on one of the selected column headers and choose "Group" from the context menu.
    • You'll see a small minus sign (-) next to the grouped columns. Clicking this will collapse the columns, effectively hiding them.
  2. Using Filters:
    • Select the range of data, including all columns.
    • Go to the "Data" tab on the ribbon.
    • Click on "Filter" to enable filters for the selected range.
    • Use the drop-down arrows in the column headers to filter out the unnecessary data. This won't hide the columns but will hide the data you don't want to see.
  3. VBA (Macro) Solution:
    • If you need a more automated solution, you can use VBA (Visual Basic for Applications) to create a macro that hides specific columns.
    • Press Alt + F11 to open the Visual Basic for Applications editor.
    • Insert a new module (Insert > Module).
    • Copy and paste the following code:

vba code is untested, please backup your file first.

Sub HideColumns()
    ' Specify the columns you want to hide
    Dim columnsToHide As String
    columnsToHide = "C:D,F:H,J:Z"
    
    ' Split the column ranges
    Dim colArray() As String
    colArray = Split(columnsToHide, ",")
    
    ' Loop through the array and hide columns
    Dim i As Integer
    For i = LBound(colArray) To UBound(colArray)
        Columns(colArray(i)).EntireColumn.Hidden = True
    Next i
End Sub
    • Customize the columnsToHide variable with the columns you want to hide, separated by commas.
    • Run the macro (Run > Run Sub/UserForm), and it will hide the specified columns.

Remember that using VBA requires some basic programming knowledge. Make sure to save your work before running any macro, and always test it on a copy of your data to avoid unintended consequences.

Choose the method that best fits your needs and comfort level with Excel features or VBA.

The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Thanks Nikolino. The VB code really helped me.
RazEllen