VBA References

Referencing in VBA is most important operation due to this operation we perform various tasks. In VBA we perform various types of Referencing operations

Such as :

• ·         Referencing with cell
•       Referencing with one cell to another cell
•       Referencing to Row
•       Referencing to Column
•       Referencing to Selection

Referencing with cel 

During this We perform our operation referencing to cell.   

     

Code :

Sub referencingCells()

           ThisWorkbook.Sheets(“Sheet1”).cells(2,1).value=”This is Referencing to Cell”

End Sub

       OutPut:

Referencing with One Cell with Another Cell

We reference our cell from one cell to another cell i.e by using this concept we take value of one cell into another cell.

Note :

This operation can be take place inside same sheet or inside another sheet also.

Inside Same Sheet

Note : Run a Micro use Alt + F8 Key

Code:

Sub referencingToSameSheet()

         ThisWorkbook.Sheets(“Sheet1”).Cells(2,1).value=

                                              ThisWorkbook.sheets(“Sheet1”).cells(3,2).value

End Sub

OutPut :

Referencing One sheet to another Sheet

Same process will be take place in this case but only changing into referencing sheet

e.g

Here sheet 2 is blank

Code:

Sub referencingToAnotherSheet()

     

           ThisWorkbook.Sheets("Sheet2").Cells(3, 2).Value=

                                                    ThisWorkbook.Sheets("Sheet1").Cells(2, 1).Value

    

End Sub

OutPut :

Referencing to Row

We explain Referencing Row with example i.e Hiding Row ,Showing Column
Here is our Original sheet

Hiding Row in Excel using VBA

Code

Sub hideRow()
                
         ThisWorkbook.Sheets("Sheet2").Rows("2:2").Hidden = True

End Sub

OutPut :

In this Sheet (2:2) is hidden

Show Row in Excel using VBA

In this case we unhide row(2:2) which is hidden in above sheet

Code :
  
 Sub showRow()
                  
                   ThisWorkbook.Sheets("Sheet2").Rows("2:2").Hidden = False
  
 End Sub

OutPut :

Similarly, In next Section we will discussed Hiding Column and Showing Column

Hiding Column in Excel using VBA

It is a complete sheet as showing below and from this sheet we hide column B using VBA code

Code :

Sub hideColumn()

      ThisWorkbook.Sheets("Sheet2").Columns("B:B").Hidden = True

End Sub

OutPut :

Showing Column in Excel using VBA

Press Alt +F8 on Excel Sheet

Code:

Sub showColumn()

                ThisWorkbook.Sheets("Sheet2").Columns("B:B").Hidden = False

End Sub

OutPut: