VBA Select Range or Cells in Excel – Excel VBA Automation

VBA Select Range

Introduction/Problem Statement

When we work with excel, many times we want to VBA select range or cell in a worksheet. Here below, we have shared many ways to manipulate the selected range easily.

Solution:

Select a single cell:

This code will select the cell A4 in the given worksheet. This code only works if sheet1 is active sheet if sheet1 not active then it shows error.

Select a single cell

Sub KDataScience_Select_SingleCell()
    
    'To select cells A4
    Worksheets("Sheet1").Cells(4, 1).Select

End Sub

If we want to select a sheet that is not an active sheet, then first we need to activate that sheet. This code will select the range A2 of sheet2.

Activate Sheet

Sub KDataScience_Select_Cell()
    
    'To Activate sheet
    Worksheets("Sheet2").Activate
    'To select Range A2
    Worksheets("Sheet2").Cells(2, 1).Select

End Sub

[ss_click_to_tweet tweet=”Everything is easy if you are crazy. Nothing will be easy if you are lazy.” content=”Everything is easy if you are crazy. Nothing will be easy if you are lazy.” style=”default” link=”1″ via=”1″]

Select a range of cells of the Active Sheet:

This code will select the range (“A2:B20”) of the active sheet.

Select a range of cells of active sheet

Sub KDataScience_Select_Range()
    
    ActiveSheet.Range("A2:D20").Select
    
End Sub

Select a Range of Non-Contiguous Cells:

We can select a group of cells that are not next to each other. This code will select cells A2,A4,D4 in the active sheet.

Select a Range of Non-Contiguous Cells

Sub KDataScience_Select_Non_Contiguous_Cells()
    
    ActiveSheet.Range("A2, A4, D4").Select

End Sub

Select All the Cells in a Worksheet

This Code will select all cells of the worksheet.

Select All the Cells in a Worksheet

Sub KDataScience_Select_AllCells()
    
    Cells.Select
    
End Sub

Select a Named Range in Excel:

This code will select “Name” range cells. But before using this code user must define a named range in “Name Manager”.

Select a Named Range in Excel

Sub KDataScience_Select_NamedRange()
    'To select a range name 'Name'
    Range("Name").Select

End Sub

Manipulating the Selection Object

We can easily manipulate the selected cells. This code will select the range A2:D10 and change the background color to red.

Manipulating the Selection Object

Sub KDataScience_Manipulate_Selected_Range()
    
    'To select the range
    ActiveSheet.Range("A2:D10").Select
    'To change the background color or slected range
    Selection.Interior.Color = vbRed

End Sub

Conclusion:

If you want to VBA Select Range or cell in a worksheet then you can use any code shown above(according to need). This code will very useful if you want to manipulate the selected range.

Leave a Reply

Your email address will not be published. Required fields are marked *