fasadlifestyle.blogg.se - How do i hide columns in excel spreadsheet

#HOW DO I HIDE COLUMNS IN EXCEL SPREADSHEET HOW TO#
#HOW DO I HIDE COLUMNS IN EXCEL SPREADSHEET CODE#

In other words, we want to hide the names of the male employees when that happens. Similarly, we want only the female employee names to be displayed when cell E2 contains the value “F”. In other words, we want to hide the names of female employees. We want only the male employee names to be displayed when cell E2 contains the value “M”. In the above dataset, we have a list of male and female employee names in two separate columns (A and B). In this example, we have the following dataset: So, let’s now take a look at another example that demonstrates this. However, most of the time, we want to hide columns on-the-fly, based on the value in a particular cell. In the first example, the columns are hidden only when the macro runs. Hiding Columns in Real-time Based on a Cell Value You can run this macro in exactly the same way as HideCols. In other words, we set the ‘Hidden’ attribute for the column to False, because we want Excel to unhide (or display) the corresponding columns containing an ‘X’ in row 8. Notice all we did is change line 5 from: = True

#HOW DO I HIDE COLUMNS IN EXCEL SPREADSHEET CODE#

Copy and paste the following code into it: Sub UnhideCols() Repeat the same steps as above to create a new macro. All you need to do is make a small change to the HideCols function. Now what do we do if we want to see the hidden rows again? Un-hiding Columns Based on Cell Value when Macro is Executed In this way, the above code hides all the columns containing an ‘X’ in row 8.

Line 8 simply demarcates the end of the HideCols sub-routine.

If the cell contains the value “X”, then we set the ‘Hidden’ attribute of the entire column (corresponding to that cell) to True, which means we want to hide the entire corresponding column.

In lines 3 to 7, we looped through each cell in row “8” of the Active Worksheet.

In line 2 we defined a variable called cell, which can refer to a range of cells.

In line 1 we defined the function name.

Let us take a few minutes to understand this code

You should see all the columns marked with an X in row 8 hidden (columns B and D).

Select the macro (or module) named ‘HideCols’ and click on the Run button.

This will open the Macro Window, where you will find the names of all the macros that you have created so far.

Click on the Macros button (under the Code group).

We want to hide columns for Monday and Wednesdays (Columns B and D), so we added an X in cells B8 and D8.

Put X’s in row 8 for all the rows that you want to hide.

Now whenever you need to use it, you simply need to run it. Select Customize Ribbon and check the Developer option from Main Tabs. Note: If you can’t see the Developer ribbon, from the File menu, go to Options. But you can replace the row number from “8” in line 3 to the row number that you plan to put your ‘X’s in.

In our example, we want to hide the columns that contain an ‘X’ in row 8.

Copy the above lines of code and paste them into the new module window. You should see a new module window open up.

Make sure ‘ThisWorkbook’ is selected under the VBA project with the same name as your Excel workbook.

If you don’t see the Project Explorer, click on View->Project Explorer

Once your VBA window opens, you will see all your project files and folders in the Project Explorer on the left side.

From the Developer menu ribbon, select Visual Basic.

To enter the above code, all you have to do is copy it and paste it in your developer window. Writing the VBA Codeįor Each cell In ("8").Cells

It should essentially analyze each cell from A8 to F8 and adjust the ‘Hidden’ attribute of the column that you want to hide. Say you have columns containing sales figures for Monday through Friday, and you want to run a macro to hide all columns that have the letter X in row 8.įor this, we need a macro that will loop through each cell of row 8 and hide the corresponding column. Let us use the following dataset to demonstrate: It can be a number, a letter, a word, or even a phrase. The value, based on which you want to hide the columns, can be anything you like.

#HOW DO I HIDE COLUMNS IN EXCEL SPREADSHEET HOW TO#

In this example, we will show you how to hide all columns that contain a particular value in a given cell. Hiding Columns Based on Cell Value when Macro is Executed

Hiding Columns in Real-time Based on a Cell Value.

Un-hiding Columns Based on Cell Value when Macro is Executed.

Hiding Columns Based on Cell Value when Macro is Executed.