There’s also another method that you can use to activate a cell.
#How to find a range in excel 2010 code
I am finding the Pearson module plugs right in and starts working in Excel 2010. This code tells VBA to select cell A5 and if you want to select a range of cells then you just need to refer to that range and simply add.
![how to find a range in excel 2010 how to find a range in excel 2010](https://i.ytimg.com/vi/3FenWi-Urz4/maxresdefault.jpg)
![how to find a range in excel 2010 how to find a range in excel 2010](https://db-excel.com/wp-content/uploads/2019/01/excel-spreadsheet-tutorial-2010-regarding-open-office-spreadsheet-tutorial-and-how-to-use-excel-2010-for.jpg)
The function distinct values is by Pearson. ResultArray = DistinctValues(InputValues:=InputRange, IgnoreCase:=True)įor Ndx = LBound(ResultArray) To UBound(ResultArray)ĭebug.Print "ERROR: " & CStr(ResultArray)ĭebug.Print "UNEXPECTED RESULT: " & CStr(ResultArray) Set InputRange = Range("onlytheusedrange") To get the lay of the landscape I need to get the range so I can plug it into this subroutine: Sub Test() I suppose I could just select the whole range of allĦ5536 rows but that as well seems like it would be overkill. The reason I ask is so I don't go reinventing the wheel. Find to return a range ofĪll cells like a certain string that is potentially modifiable but it seems like there must be some generally accepted method already to do this. I have some code from Hans already that uses. I am finding that I have to return a range of the cells that have been used in a column. Google Docs also supports the feature.I had some ADO code that I am converting over to something that functions better with Excel. In this example, weve entered OrderID as the name for the range. That is, Excel looks for an exact match for 15 in B1 through B5. The name can be up to 255 characters in length. Therefore, Excel matches the first argument (15) with values in the cells of the second argument (cell range B1 through B5), looking for an exact match (the third argument in the MATCH function - 0 - means 'find the first exact match'). When the New Name window appears, enter a descriptive name for the range. If they type the wrong data, they will get the error pop as configured above in Step 5 (if you configured an error message, etc.).Īs I mentioned in the opening, I use drop-down lists all the time at work and at home when coordinating articles here at groovyPost. Then select the Formulas tab in the toolbar at the top of the screen and click on the Define Name button in the Defined Names group. If the user does the drop-down and selects, no problem. These cells will now also be moderated/normalized by data validation, so you will only be allowed to enter one of the source options into the cell. Now all of the cells you initially chose will have a dropdown menu function with a list you can choose from for the cell.
![how to find a range in excel 2010 how to find a range in excel 2010](https://i.stack.imgur.com/LaZBB.png)
The second is the Error Alert, this will let you set up a message when someone attempts to input information in the cell that does not match what you put in the source. The first one is Input Message this tab will allow you to assign a small pop-up message that appears whenever someone selects a cell with this data validation assigned to it. There are two additional Data Validation tabs that you can use if you like.
#How to find a range in excel 2010 how to
For instructions for modern versions of Excel, read our article: How to Create Drop-Down Lists Using Data Validation in Microsoft Excel. Update: This article is for Excel 2007 & 2010.