Excel VBA to Hide Columns Using Column Number (6 Examples)

This article will teach us to hide columns using the column number with VBA in Excel. Usually, excel expresses columns with alphabets. But we can also represent columns with numbers in a VBA code. Users often need to hide Excel columns or rows to protect data or remove unnecessary information. We can easily hide columns using the built-in features of Excel. But, today we will show 6 ideal examples. Using these examples, you can hide columns using column numbers in Excel.


Download Practice Book

You can download the practice book from here.


6 Excel VBA Examples to Hide Columns Using Column Number

To explain these examples, we will use a dataset that contains information about the ID, Name, and Department of some employees of a company. We will try to hide Columns B, C, and D using column numbers.


1. Excel VBA to Hide Columns Using Column Number in Range Object

In the first example, we will hide columns using column numbers in a range object. Generally, you can’t directly use a column number inside a range object. But we can insert the column number inside the cell object. Let’s follow the steps below to see how we can implement the VBA code.

STEPS:

  • In the first place, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
  • Alternatively, you can press Alt + F11 to open the Visual Basic window.

Excel VBA to Hide Columns Using Column Number in Range Object

  • Secondly, select Insert in the Visual Basic window. A drop-down menu will appear.
  • Select Module from there.

Excel VBA to Hide Columns Using Column Number in Range Object

  • Now, type the below code in the Module window:
Sub Hide_Using_Range_Object()
Range(Cells(4, 3), Cells(9, 3)).EntireColumn.Hidden = True
End Sub

Excel VBA to Hide Columns Using Column Number in Range Object

In this code, we have selected a range C4:C9 with the column number. Here, ‘Cells(4,3)’ denotes Cell C4, and ‘Cells(9,3)’ denotes Cell C9. The ‘Cells()’ object has two arguments inside it. One is the row number and the second one is the column number. So, using this code, we have hidden Column 3 or Column C.

  • Press Ctrl + S to save the code.
  • After that, you can press the F5 key to run the code.
  • Otherwise, you can go to the Developer tab and select Macros. It will open the Macro window.

Excel VBA to Hide Columns Using Column Number in Range Object

  • In the following step, select the code in the Macro window and Run it.

Excel VBA to Hide Columns Using Column Number in Range Object

  • Finally, you will be able to hide Column C using the column number.

Excel VBA to Hide Columns Using Column Number in Range Object

Read More: How to Hide Selected Columns in Excel (5 Easy Methods)


2. Use Number in Columns Property to Hide

To use the column number directly, we can use the columns property. We don’t need to use the cell object this. Here, we will use the previous dataset and hide Column C again. So, let’s pay attention to the steps below to implement the code.

STEPS:

  • Firstly, go to the Developer tab and click on Visual Basic to open the Visual Basic window.
  • In the second step, select Insert and then, click on Module to open the Module window.
  • After that, type the code in the Module window:
Sub Hide_Using_Columns_Property()
Columns(3).EntireColumn.Hidden = True
End Sub

Use Number in Columns Property to Hide

This code hides Column C. It is the third column of the excel sheet. So, we have typed 3 inside the columns property. You need to type the column number you want to hide in place of the 3.

  • Now, press Ctrl + S to save the code.
  • In the following step, press the F5 key to run the code.
  • After running the code, you will see results like the picture below.

Use Number in Columns Property to Hide

Read More: Excel VBA: Hide Columns Based on Cell Value (15 Examples)


3. Hide Multiple Columns Using Number in Excel VBA

We can hide multiple columns easily using the range object. But to hide using column numbers, you need to repeat the command of columns property. This process is also straightforward. Once again, we will use the same dataset but this time we will hide Columns C and D using column numbers. So, let’s follow the steps below to learn the method.

STEPS:

  • First of all, navigate to the Developer tab and click on Visual Basic. It will open the Visual Basic window.
  • Secondly, select Insert and then, click on Module to open the Module window.
  • After that, type the code in the Module window:
Sub Hide_Multiple_Columns()
Columns(3).EntireColumn.Hidden = True
Columns(4).EntireColumn.Hidden = True
End Sub

Hide Multiple Columns Using Number in Excel VBA

This code is similar to the code of Example2. If you need to hide another column, suppose Column E, then you need to add Columns(5).EntireColumn.Hidden = True before the End Sub command.

  • Press Ctrl + S to save the code.
  • After saving the code, hit the F5 key to run it.
  • Finally, you can hide multiple columns like the picture below.

Hide Multiple Columns Using Number in Excel VBA

Read More: How to Hide and Unhide Columns in Excel (7 Quick Methods)


4. Excel VBA to Hide Alternative Columns Using Column Numbers

In this example, we will hide the alternative columns. In our case, Columns C and E are the alternative columns. We will hide these columns using column numbers.

Excel VBA to Hide Alternative Columns Using Column Numbers

Let’s observe the steps below to see how we can implement the VBA code.

STEPS:

  • In the beginning, go to the Developer tab and click on Visual Basic. It will open the Visual Basic window.
  • Secondly, select Insert and then, click on Module to open the Module window.
  • After that, type the code in the Module window:
Sub Hide_Altenative_Columns()
Dim i As Integer
For i = 2 To 6
    Cells(1, i + 1).EntireColumn.Hidden = True
    i = i + 1
Next i
End Sub

Excel VBA to Hide Alternative Columns Using Column Numbers

Here, we have used the cell object to insert the column number. We have declared an integer variable i. Then, we used a ForNext loop. The values of cells are Cells(1,3) and Cells(1,5). They both denote Cell C1 and E1. After evaluating the cells, the code hides the entire column regarding those cells.

  • Now, press Ctrl + S to save the code.
  • After saving the code, press the F5 key to run the code.
  • In the end, the code will hide the alternative columns.

Excel VBA to Hide Alternative Columns Using Column Numbers

Read More: How to Unhide Columns in Excel All at Once (4 Quick Ways)


5. Use Column Numbers to Hide Empty Columns in Excel

In the fifth example, we will use column numbers to hide empty columns. In the beginning, we will show the code for the dataset with no merged cells. Then, we will describe the code for merged cells. Here, we will use the dataset below. You can see Columns C and D are empty.

Use Column Numbers to Hide Empty Columns in Excel

Let’s observe the steps below to learn the method.

STEPS:

  • To begin with, go to the Developer tab and click on Visual Basic to open the Visual Basic window.
  • After that, select Insert and then, click on Module to open the Module window.
  • In the following step, type the code in the Module window:
Sub Hide_Empty_Columns()
Dim i As Integer
For i = 2 To 6
    If Cells(1, i).Value = "" Then
            Columns(i).Hidden = True
    End If
Next i
End Sub

Use Column Numbers to Hide Empty Columns in Excel

This code will check for empty values in Columns B to F first and then, hide the empty columns. Here, we have again used a ForNext loop. In each loop, it will look for empty values in a column. After completing that column, it will move to the next one. The code starts from the first row, that’s why we can’t use any merged cells.

  • After typing the code, press Ctrl + S to save the code.
  • Then, hit the F5 key to run the code and you will get results like the picture below.

Use Column Numbers to Hide Empty Columns in Excel

  • Suppose, you have merged cells like the picture below. Here, Cells B2 to F2 are merged.

  • In this case, you need to apply the code below:
Sub Hide_Empty_Columns()
Dim i As Integer
For i = 2 To 6
    If Cells(4, i).Value = "" Then
            Columns(i).Hidden = True
    End If
Next i
End Sub

Here, we have started the code from row 4 to avoid the merged cells. That is why we have written Cells(4,i).

  • The output of this code will be like the picture below.

Read More: Excel Hide Columns Based on Cell Value without Macro


6. VBA to Hide Columns Using Column Number Based on Cell

In the last example, we will hide columns using the column number based on the cell. Here, Cell A1 will contain the column numbers. The code will hide the number of columns that Cell A1 will store. For this purpose, we will use the dataset below. You can see column number (2) in Cell A1.

Let’s observe the steps below to learn more.

STEPS:

  • Firstly, go to the Developer tab and click on Visual Basic. It will open the Visual Basic window.
  • In the second step, select Insert and then, click on Module to open the Module window.
  • Thirdly, type the code in the Module window:
Sub Hide_Columns_Based_on_Cell()
Dim xColumnsToHide As Integer
xColumnsToHide = Cells(1, 1).Value
Worksheets("Based on Cell").Columns(2).Resize(, xColumnsToHide).EntireColumn.Hidden = True
End Sub

This code will hide the numbers of columns from Column B. Here, Cell A1 will store the number of columns. In our case, Cell A1 contains 2. That means the code will hide Columns B and C.

  • Press Ctrl + S to save the code.
  • Then, hit the F5 key to run the code.
  • Finally, you will be able to hide Columns B and C.

Read More: Excel VBA to Hide Columns Based on Criteria (6 Useful Examples)


Conclusion

In this article, we have demonstrated 6 ideal examples of Excel VBA to Hide Columns Using Column Numbers. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the method to hide columns using column numbers based on a cell. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo