Mursalin Ibne Salehin
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.
Excel VBA Hide Columns Using Column Number: 6 Examples
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.
- Secondly, select Insert in the Visual Basic window. A drop-down menu will appear.
- Select Module from there.
- 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
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.
- In the following step, select the code in the Macro window and Run it.
- Finally, you will be able to hide Column C using the column number.
Read More: How to Hide Selected Columns in Excel
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
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.
Read More: Excel VBA: Hide Columns Based on Cell ValueÂ
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 the 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
This code is similar to the code of Example–2. 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.
Read More: How to Hide and Unhide Columns in Excel
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.
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
Here, we have used the cell object to insert the column number. We have declared an integer variable i. Then, we used a For–Next 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.
Read More: How to Unhide Columns in Excel All at Once
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.
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
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 For–Next 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.
- 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 number 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
Download Practice Book
You can download the practice book from here.
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.
Related Articles
- How to Hide Columns with Button in Excel
- Unhide Columns in Excel Shortcut Not Working
- Excel Hide Columns with No Data
- How to Unhide All Columns with Excel VBA
- Unhide Columns Is Not Working in Excel
- How to Collapse Columns in Excel
- How to Group and Hide Columns in Excel
- How to Hide Columns in Excel with Password
- How to Hide Extra Columns in Excel
- How to Hide Columns Without Right Click in Excel
- How to Unhide Columns in Excel
