VBA Select Range or Cells in Excel – Excel VBA Automation

VBA Select Range
  • Save

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
  • Save

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
  • Save

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

End Sub

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
  • Save

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
  • Save

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
  • Save

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
  • Save

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
  • Save

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.

Don't miss out!
Subscribe To Newsletter

Get the latest Excel, VBA, Automation, Tips & Tricks, delivered straight to your inbox.

Invalid email address
Give it a try. You can unsubscribe at any time.

Leave a Reply

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

Copy link
Powered by Social Snap