Excel-VBA : Range – Lock/Unlock Specific Cells

There are two different cases you might deal with,

  • Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)
  • Lock the Enitre worksheet except few cells

Lock only few specific cells and rest of the cells in the work sheet are not locked

Steps:

  • Lock the selected cells
  • Provide the password

Note: If you wont provide the password then your cells will not be locked.

Read moreExcel-VBA : Range – Lock/Unlock Specific Cells

VBA-Excel: Cells Ranges Offset – Active Cell

ActiveCell, as the name clearly indicates that the cell which is presently active in your worksheet, in other words you can if u start typing the value u entered will go to active cell.

Example:

Function FnActiveCell()

         Dim mainWorkBook As Workbook

         Set mainWorkBook = ActiveWorkbook   

         mainWorkBook.Sheets("Sheet1").Activate

     ActiveCell.Value = 5

Read moreVBA-Excel: Cells Ranges Offset – Active Cell

VBA-Excel : 3D-Ranges – FillAcrossSheets Method

 

By using FillAcrossSheets you can copy the Range of one worksheet to other multiple worksheets.

Steps:

  • Create an array and assign multiple worksheets names ( in which the data to be copied), as an array elements.
  • Copy the range of main worksheet and paste it to the other sheets using FillAcrossSheets.

Read moreVBA-Excel : 3D-Ranges – FillAcrossSheets Method

VBA-Excel : 3D-Ranges – Working with Multiple WorkSheets At The Simultaneously

You can update the multiple worksheets at the same time. If you want to do the similar action on the same range on multiple sheets, all you need to do is use Array Function to specify multiple sheets name as array elements

Sub MultipleSheets()

    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

    Range("J8").Select

    Selection.Value = 7

Read moreVBA-Excel : 3D-Ranges – Working with Multiple WorkSheets At The Simultaneously

VBA-Excel: Select and Activate Cells – Activate

Here is something interesting for you, you can use a Activate method to activate a cell within a selection.

Activating a Cell Within a Selection

Sub ActivateCellInSelection()

   Worksheets("Sheet4").Select

   Worksheets("Sheet4").Range("A1:H6").Select

   Worksheets("Sheet4").Range("D3").Activate

End Sub

Read moreVBA-Excel: Select and Activate Cells – Activate

VBA-Excel: Select and Activate Cells – Select

Select:

You can Select a cells or cells for performing many activities like putting some value in cell or doing formatting or copy-paste the data. You can put or enter values in cells with/without selecting the cells. Select is used mostly in the cases of copy-paste operation where you have to tell the compiler specifically that from which cell it has to copy the data and in which cell it needs to be pasted.

Note:

Select works only with actives worksheets, if you use Select on cells before activating the respective sheet, the select method will fail and you will get “Run-time error ‘1004’: Application defined or object defined error”

Read moreVBA-Excel: Select and Activate Cells – Select

VBA Excel – Refer to Multiple Ranges : Union Method

You can combine more than one Ranges into one Range using Union Method and later on instead of writing all the ranges repeatedly, just use the combined one. Example Sub CombileMultipleRange() Dim objCombinedR as Range Set R1 = Sheet1.Range(“H1”)    Set R2 = Sheet1.Range(“M2”) Set objCombinedR = Union(R1, R2) objCombinedR.Interior.ColorIndex = 44 End Sub Similarly you … Read more VBA Excel – Refer to Multiple Ranges : Union Method

VBA Excel – Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation

You don’t have to type Range every time, you have shortcut for that, ‘Brackets’.

Shortcut for referring one cell.

Worksheets(“Sheet1”).[G5].Interior.ColorIndex = 45

Instead of using Range(“G5”), you can use [G5].

Shortcut for referring range of cells.

Worksheets(“Sheet1”).[E3:F5].Value = 4

Instead of using Range(“E3:F5”), you can use [E3:F5].

Read moreVBA Excel – Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation

VBA Excel – Cells, Ranges and Offset: Refer Range by using A1 Notations

In the earlier tutorial (Range), you have seen how you use Range property. In this tutorial you will see the many different ways to use Range property to refer the excel sheet’s rows and columns.

Reference – Range (“A1”)

–          Refer Cell A1

Reference – Range (“A1:D10”)

–          Refer all the cells from A1 to D10 ( The entire MxN matrix)

Reference – Range(“A1:C3”,”D5:H7”)

–          Refer all the cells from A1 to C3 and D5 to H7

Read moreVBA Excel – Cells, Ranges and Offset: Refer Range by using A1 Notations

VBA-Excel: Delete Blank Rows from Excel Work Sheet

 

Sometimes deleting the blank rows from you Excel sheet is a tedious task to do especially when your sheet contains lots of data, say 10k-15k rows and having some blank rows in between and you need to delete these rows. Just imagine to delete these blank rows manually, but VBA Codes are life saver here.

  • Open a new Excel WorkBook and press “Alt+F11” to open the Visual Basic Editor
  • Copy Paste the following code
Sub FnDeleteBlankRows()
   Dim mwb As Workbook
   Set mwb = ActiveWorkbook 
   For x = mwb.Sheets("1").Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1 
      If WorksheetFunction.CountA(mwb.Sheets("1").Rows(x)) = 0 Then
         mwb.Sheets("1").Rows(x).Delete
      End If
   Next 

Read moreVBA-Excel: Delete Blank Rows from Excel Work Sheet

VBA Excel – Cells, Ranges and Offset : Offset

Offset:

  • Offset property in VBA-Excel is used along with Range.
  • With the help of Offset property user can move around in the sheet.

Format:

Range(“StartingPoint”).Offset(NoOfRowsToBeMoved , NoOfColumnsToBeMoved)

Example :

Range(“A2”).Offset(1,0).Select

And compiler reads, move one cell down from A2 , which is A3.

Range(“A2”).Offset(-1,0).Select

Read moreVBA Excel – Cells, Ranges and Offset : Offset

VBA Excel – Cells, Ranges and Offset : Range

Format: Range (“ColumnNameRowNumber”)

Ex: In VBA-Excel when you say Range(“A5”) it’s very easy to understand that you are talking about 5th row in 1st column (“A”)

As the name states Range, you can select a range is cells using this function

Ex: Range (“A1:B5”).Copy means copy all the cells between A1 to B5

To know more about Copy/Paste in VBA-Excel click here (link to VBA-Excel Copy Paste)

OR

Range (“A1, A5, A9, C1:C8”).Select

Read moreVBA Excel – Cells, Ranges and Offset : Range

VBA Excel – Cells, Ranges and Offset : Cells

Format : Cells(Row,Column)

Excel is represented in a grid format, tabular form in rows and columns, so to reach to particular cell you need tell to compiler the cell is located in which row and which column in that particular row.

Ex: in VBA Excel when you say Cells(2,3) which means, in 2nd row and in 3rd column means “C2”

Some of the very effective functions of Cells are

  • Cells.Select
  • Cells.ClearContents
  • Cells.SpecialCells(xlCellTypeLastCell) (Ex: Delete Blank rows)

Read moreVBA Excel – Cells, Ranges and Offset : Cells