Strip chat with bianca - Vba no screen updating
A frequently asked question is how to hide rows and/or columns in Excel using a VBA macro. To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module.
The other approach is to run an "ordinary" macro that checks if certain conditions are met. The first example shows how to hide empty columns, and the next example shows how to use the search function and hide rows, if a certain value exists in a given range. So you don't need to do anything active like clicking a button or the like to run the code.
However before we start playing hide and seek, I want to show you how to make the hidden rows and columns visible again! 'If you want to search the entire worksheet, you write 'e.g. You can do that by putting VBA code into the worksheet's own code sheet. We use the standard procedure "Worksheet_Change", which is called automatically when something changes in the worksheet.
The following example uses Excel's search function in a given range - here column B, but that can easily be changed. If you cannot see the worksheets code sheet in the VBA editor press CTRL+R to open Project Explorer: The following Worksheet_Change procedures must be in the worksheet's code sheet to work.
We ask the user what to search for, and a row is hidden if it has the seach value in column B. The first example is very simple: If you write an "x" in column A, the row with the "x" will be hidden.
If you write a 0 (zero) all hidden rows will be visible again, and old values in column A are deleted.