Visual Basic for Applications (VBA) allows users to simplify every step of the process in Excel. We can benefit from it by automating every single step.
In the example below, we will show how to hide rows and columns in Excel with VBA, and then how to select only the cells that are not visible for further manipulation.
Hide Rows and Columns
For the example, we will use simple data representing every row in which the text is located:
To open the VBA, we will click ALT + F11 on our keyboard, then go to Insert >> Module on the screen that appears:
In the module, we will insert the following code:
|
1 2 3 |
Sub Hide_Rows() Range("2:4").EntireRow.Hidden = TRUE End Sub |
This is what it looks like in the module:
This code is pretty simple, and, when executed, it will hide rows 2,3, and 4. If we wanted to do similar things to columns, we would have changed the range to reflect columns, instead of rows, so it would be something like Range(“A:C”) or similar.
We will execute the code by pressing F5 on our keyboard while in the module and our rows will be hidden:
Select Visible Cells Only Using VBA
If we would simply go on and select range: A1:E5 and copy it, we would have an issue because even the hidden cells would be selected.
To resolve this issue, we will create the following code:
|
1 2 3 4 5 6 7 8 |
Sub Select_Visible_Cells() Dim r As Range Set r = Range("A1:E5") r.SpecialCells(xlCellTypeVisible).Select r.SpecialCells(xlCellTypeVisible).Copy Range("A8").Select ActiveCell.PasteSpecial End Sub |
Most of the things in the code above should be familiar. We declare one variable, which is r, and with the type range. Then we set it to be equal to our range.
Then we use the SpecialCells function which has two parameters: type as XLCellType and value. In our case, since we already have values (which is our range) we did not define the value.
For the final part of the code, we will copy and paste our range starting with cell A8. When we execute this code (by pressing F5 on the keyboard) this is the result we end up with:
As seen, only the rows that are visible to us were copied.