VBA Hide unwanted columns on multiple sheets or a workbook

[usui]

Hi guys can anyone help me create a VBA code to hiding unwanted columns not needed, i want to enter the specific column header only that I want to remain on the sheets,

Sample: I want "Tea" (can be change anytime) to remain on all the sheets because this is a workbook, and the rest o the columns will be hidden so that only the Tea will remain
The search should only start on Column H, so meaning starting from column "H" all the columns aside from Tea will be hidden.


I hope someone can help me with this, and hoping to make it work on all the sheets in the workbook.

 

[Saurabhj]

Hi,

I created a sheet "main" in the workbook and in cell B2 typing the column heading (e.g. "tea") which we want to show.

The below code will compare the column headings of all sheets with the column heading available in B2 cell of main worksheet.

Sub hideColumns()
Dim lCol As Integer
Dim shCount As Integer, sh As Integer, colno As Integer

shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    If Sheets(sh).Name <> "main" Then
    
        lCol = Sheets(sh).Cells(1, Columns.Count).End(xlToLeft).Column
        For colno = 8 To lCol
            If Sheets(sh).Cells(1, colno) <> Sheets("main").Range("B2") Then
                Sheets(sh).Columns(colno).EntireColumn.Hidden = True
            End If
        Next
    End If
    
Next
End Sub

Book1
	A	B	C	D
1
2		tea
3
4
5
6
7
8
9
10
11
main

 

[usui]

Hi,

I created a sheet "main" in the workbook and in cell B2 typing the column heading (e.g. "tea") which we want to show.

The below code will compare the column headings of all sheets with the column heading available in B2 cell of main worksheet.

Sub hideColumns()
Dim lCol As Integer
Dim shCount As Integer, sh As Integer, colno As Integer

shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    If Sheets(sh).Name <> "main" Then
   
        lCol = Sheets(sh).Cells(1, Columns.Count).End(xlToLeft).Column
        For colno = 8 To lCol
            If Sheets(sh).Cells(1, colno) <> Sheets("main").Range("B2") Then
                Sheets(sh).Columns(colno).EntireColumn.Hidden = True
            End If
        Next
    End If
   
Next
End Sub

Book1
	A	B	C	D
1
2		tea
3
4
5
6
7
8
9
10
11
main

Not working for me. Headers are located at Row 3

 

[usui]

Hi,

I created a sheet "main" in the workbook and in cell B2 typing the column heading (e.g. "tea") which we want to show.

The below code will compare the column headings of all sheets with the column heading available in B2 cell of main worksheet.

Sub hideColumns()
Dim lCol As Integer
Dim shCount As Integer, sh As Integer, colno As Integer

shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    If Sheets(sh).Name <> "main" Then
   
        lCol = Sheets(sh).Cells(1, Columns.Count).End(xlToLeft).Column
        For colno = 8 To lCol
            If Sheets(sh).Cells(1, colno) <> Sheets("main").Range("B2") Then
                Sheets(sh).Columns(colno).EntireColumn.Hidden = True
            End If
        Next
    End If
   
Next
End Sub

Book1
	A	B	C	D
1
2		tea
3
4
5
6
7
8
9
10
11
main

I have a working code here but it only works on 1 sheet..not on the other sheets..can you help me with this instead?


Sub MyDeleteColumns()
Dim ColumnsToHide As String, V As Variant
Dim xSh As Worksheet

Application.ScreenUpdating = False

' Prompt for name of column
colHdr = InputBox("Enter name of column that you would like to keep")

' Find last column in row 3
lc = Cells(3, Columns.Count).End(xlToLeft).Column

' Loop through all columns backwards and delete if not equal to entered in name
For c = lc To 8 Step -1
If Cells(3, c).Value <> colHdr Then Columns(c).Delete
Next

Application.ScreenUpdating = True

End Sub

 

[Saurabhj]

Hi, In my case it was row no 1. Check below.

Sub hideColumns()
Dim lCol As Integer
Dim shCount As Integer, sh As Integer, colno As Integer

shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    If Sheets(sh).Name <> "main" Then
   
        lCol = Sheets(sh).Cells(3, Columns.Count).End(xlToLeft).Column
        For colno = 8 To lCol
            If Sheets(sh).Cells(3, colno) <> Sheets("main").Range("B2") Then
                Sheets(sh).Columns(colno).EntireColumn.Hidden = True
            End If
        Next
    End If
   
Next
End Sub

 

[Saurabhj]

I have a working code here but it only works on 1 sheet..not on the other sheets..can you help me with this instead?


Sub MyDeleteColumns()
Dim ColumnsToHide As String, V As Variant
Dim xSh As Worksheet

Application.ScreenUpdating = False

' Prompt for name of column
colHdr = InputBox("Enter name of column that you would like to keep")

' Find last column in row 3
lc = Cells(3, Columns.Count).End(xlToLeft).Column

' Loop through all columns backwards and delete if not equal to entered in name
For c = lc To 8 Step -1
If Cells(3, c).Value <> colHdr Then Columns(c).Delete
Next

Application.ScreenUpdating = True

End Sub

Check below after few change in your code:

Sub MyDeleteColumns()
Dim ColumnsToHide As String, V As Variant
Dim xSh As Worksheet, sh As Integer

Application.ScreenUpdating = False

' Prompt for name of column
colHdr = InputBox("Enter name of column that you would like to keep")


shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    ' Find last column in row 3
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    
    ' Loop through all columns backwards and delete if not equal to entered in name
    For c = lc To 8 Step -1
        If Cells(3, c).Value <> colHdr Then Columns(c).Delete
    Next
Next
Application.ScreenUpdating = True

End Sub

 

[My Aswer Is This]

Each sheet has more then 16,000 columns.
I would think hiding all these columns except for 1 in all the sheets in your workbook would be a large tasking

 

[usui]

Check below after few change in your code:

Sub MyDeleteColumns()
Dim ColumnsToHide As String, V As Variant
Dim xSh As Worksheet, sh As Integer

Application.ScreenUpdating = False

' Prompt for name of column
colHdr = InputBox("Enter name of column that you would like to keep")


shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    ' Find last column in row 3
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
   
    ' Loop through all columns backwards and delete if not equal to entered in name
    For c = lc To 8 Step -1
        If Cells(3, c).Value <> colHdr Then Columns(c).Delete
    Next
Next
Application.ScreenUpdating = True

End Sub

Still the same only work on 1 sheet..so i need to do it manually each sheet...is their any code to be add so that we can only enter the text once and it will do it to all the sheets in the workbook?

 

[usui]

Hi, In my case it was row no 1. Check below.

Sub hideColumns()
Dim lCol As Integer
Dim shCount As Integer, sh As Integer, colno As Integer

shCount = ThisWorkbook.Sheets.Count

For sh = 1 To shCount
    If Sheets(sh).Name <> "main" Then
  
        lCol = Sheets(sh).Cells(3, Columns.Count).End(xlToLeft).Column
        For colno = 8 To lCol
            If Sheets(sh).Cells(3, colno) <> Sheets("main").Range("B2") Then
                Sheets(sh).Columns(colno).EntireColumn.Hidden = True
            End If
        Next
    End If
  
Next
End Sub

it still did not work for me only on 1 sheet

 

[usui]

Each sheet has more then 16,000 columns.
I would think hiding all these columns except for 1 in all the sheets in your workbook would be a large tasking

only for active columns