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 screenupdating being set to false will also speed up our macro as Excel will not try to repaint the screen each time it changes.
Subs: Delete Blank Rows1, Delete Blank Rows3 and both Worksheet_Change events are slightly different as they first check to see if the ENTIRE row is blank.
If the screen cannot be scrolled to center on a cell (e.g., you can't center on C5 because there are not enough rows above row 5 or columns to the left of column C to fill out the screen), it comes as close as possible.
The reason we turn off calculation is in case the range in which we are deleting rows contains lots of formulas, if it does Excel may need to recalculate each time a row is deleted, slowing down the macro.
Sub Delete Blank Rows1() ' Deletes the entire row within the selection if the ENTIRE row contains no data.
' We use Long in case they have over 32,767 rows selected. Screen Updating = False ' We work backwards because we are deleting rows.
To unzip, right-click and select whatever Windows suggests to unzip the archive. You can do it "automatically" by putting code in the worksheet's code sheet.
The Work Sheet_Change procedure is activated every time something is changed in the worksheet. The next two macros are examples of what I call "automatic code," because they execute automatically, if a certain condition is met.