Select Cell Range in Excel VBA

Home/Excel VBA/Select Cell Range in Excel VBA

Description:

If we want to deal with Cell or Range we can directly mention the object and change its properties or call its methods. We can also deal with Cells or Range by simply selecting it. Then we can use Selection object to refer Selected Cells, Range or any other Objects.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


We select cell range in Excel VBA to do particular task with the selection. For example we may want to change the background color of a range and we may want to change the font to Bold. In this case we do not required to loop each and every cell of the range to change the background color and font.

Select Cell Range in Excel VBA – Solution:

Select Cell Range in Excel VBA
We can use Select method of Range or Cell to select it and do whatever you want to do.

Select Cell Range in Excel VBA – Example:

Following are the various examples which will show you how to select a range and perform some task.

Example to Select a Cell

In this example I am selecting a Cell using Select method of Cell.

Sub sbSelectACell()
'Selecting a Cell
Cells(2, 3).Select 'This will select the Cell at 2nd row and 3rd column
End Sub
Example to Select a Range

In this example I am selecting a Range using Select method of Range.

Sub sbSelectARange()
'You can also use Range Object
Range("C3").Select
'Collection of Cells OR Multiple Cells = Range
Range ("B2:C4").Select ' It will Select B2,B3,B4,C2,C3,C4
End Sub
Example to Select a Range and change the background color

In this example I am selecting the range from B2:C4 using Select method. And changing the background color to red using ColorIndex Property of Range.

Sub sbSelectARangeandForamt()
'Selecting a Range
Range("B2:C4").Select ' It will Select B2,B3,B4,C2,C3,C4
Selection.Interior.ColorIndex = 3
End Sub
Instructions
  1. Open an Excel Workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module from Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it
LIMITED TIME OFFER
By |February 15th, 2013|Excel VBA|10 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

10 Comments

  1. Mukesh January 19, 2014 at 9:25 PM - Reply

    Good Excellent

    • PNRao January 19, 2014 at 9:40 PM - Reply

      Hi Mekesh,

      Thanks for your feedback.

      Thanks-PNRao!

  2. bharat kumar June 23, 2014 at 1:33 PM - Reply

    Hello sir,
    i am very enthusiastic to learn vba so please guide me.i know some basics.I have seen many websites about this.I hope you are the best one to assist vba and i was so exiting while i have seen this site.I think you offered best vba module i have never seen before.I believe myself i am perfect in VBA under your guidelines.Thank you sir

    • PNRao June 23, 2014 at 10:45 PM - Reply

      Hi Bharat,

      Thanks for showing interest to learn VBA! Yes, our goal is to provide the course with complete practical examples, instead of explaining only concepts. We committed to serve our customers with the best approach to make them experts in VBA, so that their satisfaction will help us to grow in this platform.

      Thank you-PNRao!

  3. Rohini July 1, 2014 at 8:51 PM - Reply

    Hello Sir, I have been going through your website for the past few weeks and it has tremendously helped with some of my codes. Although I have some experience in Access VBA, I have not used Excel VBA that much except to create some macros to help me do my job better. I work in data analysis for a public accounting firm and I find the examples very useful. Do you encourage asking specific questions (short) about vba code? Thanks for the excellent service you are doing for the Excel users community.

    • PNRao July 1, 2014 at 9:31 PM - Reply

      Hi Rohini,

      Thanks for your feedback! You can feel free to ask questions regarding VBA or Excel. We are glad to know that our site is useful for Excel users. We are happy to help you by answering your questions.

      Thanks-PNRao!

  4. Girish September 11, 2014 at 10:53 AM - Reply

    Hi,

    I need your help to select rows from a report(row and column # are not fixed it gets changed frequently since these are filtered). Once we filter accordingly we are left with a range of info. Can u just help me out in this case..

  5. Girish September 12, 2014 at 8:52 AM - Reply

    Hi,

    I actually tried using the above codes prior asking u this. This however goes till it find a blank row/column. I actually need code the macro such that it checks the filtered data and deletes/copies the entire row. I m not sure how to crack this i m not familiar with loop iteration as well. So need your support on helping me out this.. . Thanks

  6. rajesh March 29, 2017 at 12:16 PM - Reply

    Hi,

    I just begin to learn VBA, I tried a code to copy data from specific cells in one workbook to some other workbooks, based on conditions.
    I used simple IF conditions for entire procedure. i need to copy this data every day. so I applied conditions based on date.
    now compiler is saying “Procedure is Too Long” .

    I want to know how to increment the cell with the help of a variable.
    Eg:
    day 1 data copied to E5
    day 2 data copied to E6

    Please suggest me, how to go ahead.

    Thanks
    Rajesh

Leave A Comment


Related pages


timeline spreadsheet template excelgenpact interview questions for financecreate combobox vbadml ddl sqlexcel copy cellsvba hlookuprecording minutes of a meeting templatehow to build dashboards in exceldatevalue excelhow to write macro in excelvb excel tutorialextract url from hyperlink excelrows in ms excelhow to unhide all worksheets in excelgetting rid of duplicates in exceldml meaningapplication.worksheetfunction.vlookupexcel 2010 find duplicatesmacro learning in excelunlock password in excelexcel insert rowsexcel application statusbarcheck for duplicates in excelexcel sheet protection password removerexcel vba tabexcel reverse vlookupvba pivot tablesvba codemysql oledb connection stringremove password excel workbookhow to unhide rows and columns in excel 2010vba open file dialogexcel if isblank thenvba save excelwriting an excel macrosql sorted byvba data validationexcel if clausevba controlsexcel count duplicatesvba tutorialsvba excel examples pdfvba nullmicrosoft office invoice templates for excelqlikview tutorial for beginners pdfinput in vbavlookup tutorialexcel vba application functionexcel 2013 pivot table wizardworkbooks.activatesum vlookupexcel vba remove duplicatesexcel if clausevba insertvba command buttonmessage box in vbaunprotect sheet command excelexcel 2007 pivot chartexcel mysql connectionpivot table advancevba rgb color codesremove password from excel 2010 workbookvba worksheethow to find duplicates in an excel columnbuilding a dashboard in excelcalendar timeline template excelexcel formula in vbaexcel project management tracking templatestesting interview questions and answers for experienced pdfvba replace methodopen excel without macroshow to unhide rows in excel 2013merge excel rowsvlookup excel function examplesexcel 2010 macro security