My guess is that with the speed of most computers these days, the difference is negligible. Select () Marshal. Last Column with data. To reset the used range, we need to resort to using VBA. If you want to insert a single row by referencing to a range of cells you need to ensure that the. Microsoft Access is the most popular Windows database program. EntireColumn. ) Enter the formula in cell C4 and then copy the range B4:C4 down two rows. With the aid of the Excel function 'Index' you can filter the rows you want to preserve. End(xlUp) lRow = lc. Note that there are some ways to determine the last row or column with data from a spreadsheet: Range("A1"). select", but if an item is added "A68" would no longer be the last empty cell/row. Next the same formula is changes but this time the Range reference is used. Range( "A1" ). Select Selection. This is what I use in Excel that works, how can it be translated into Access?? Selection doesn't seem to be an available option in Access nor does ActiveCell appear. Thus finding last used row or column makes useful in many ways. If I have a range selected on my spreadsheet, I want to be able find the first and last row in the selection. I use the last row to set the condition to stop looping around: EndRow = Sheets("Sheet1"). Select rows and columns in an Excel table Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Mobile More. The procedure above works fine, if it is always the same range you use as rowsource, but what if it changes from time to time? Then you must use a dynamic range as rowsource in the Initialize procedure. Select This selects Row 9 to 20, 22 to 33 and 35 to 46. For demonstration purpose we have applied the formula A1*B1 in cell D1. To sort a range, you need to specify at least one key as well as the range to sort. This will help you to know how to delete specific rows in a range from Excel workbook using VBA. Select is the same as Range("AE11"). If it's a table object why can't you just do: Lr = Range("A" & Rows. 09 / Tags: VBA I have a Workbook with multiple sheets, all with the same configuration, with headers on row 1 and data starting from row 2. I am trying to find a VBA code that i can insert into a current recorded macro to select the first empty row before pasting copied information. There are quite a few ways to find and select the last row in a column using VBA and each have their merits. These can be as simple as selecting a cell on an active worksheet, selecting one on another worksheet, or selecting a range of cells on a worksheet from a different workbook. TYPING THE MACRO. In VBA we often refer the word range to select a particular cell or series of cells. CurrentRegion. Excel & VBa: find last row, column, cell in an Excel (work)sheet November 26, 2008 nabeelmoeen Leave a comment Go to comments Please visit the following site for the original Article. VBA Methods: Using VBA you can insert a new row by referencing to a single cell, a range of cells or an entire row. There are many ways to find the last row and column in VBA we will see some of them with examples. Just use the Rows method and give it the row number you’d like to refer to as an argument. This page shows examples of how you can speed things up a lot by using arrays. Count, 1) _. so can you help me to fix this issue. < Apply the formula across relevant rows > Select the range, including the formula > Home tab > Editing group > Click Sort & Filter > Click Filter > Filter for Text > Select all the rows within the range > Editing > Click Find & Select > Click Go To Special > Select Visible cells only > Click OK > Delete rows. After you do so, Excel will select every blank cell in the selected range. You can simply copy-paste them in your VBA editor and use them. Place the following functions in a public code module (one which is visible from the modules tab of the database window). If the last used row was 77 then the above formula returns a range A12 to M77 As a note about VBA - you typically don't need to 'Select' cells before doing an operation on them. Select the range in Excel to insert a new row. In the same way, to select a range or records, pass the range to the Rows collection and call the Select() method. The first thing we will look at is the mistake that most VBA beginners make - using Select. The Complete Guide to Ranges and Cells in Excel VBA by Paul Kelly · 150 Comments “It is a capital mistake to theorize before one has data”- Sir Arthur Conan Doyle. Additionally, when using the Cells property of a Range object, the row and column index numbers can be 0 or negative. Sub RangeSelect() ActiveSheet. Select This is very similar to the last kind of loop. Excel VBA Last Row. The most basic way to delete a row in VBA is by using the row number. In this usage, Cells is used twice to determine a specific range of cells. Benefit is that if you add cell between that named range, the named range adjusts automatically. Delete,and Range. To find the last row in the used range with VBA, follow these steps: Identify the worksheet whose last row you want to find (Worksheet). End Method UsedRange. Press Alt + F11 keys to open Microsoft Visual Basic for Applications window. If you routinely have data that starts in A5:G5 but can be any number of rows tall, using Selection. In this example, Looping concept in VBA along with IF Condition is used to copy entire row if condition is met and paste on another About this tutorial: Video duration: 10:44 VBA Code to Copy and Paste specific rows from one sheet to another sheet when condition is met can be used on Click Event of Command Button. VBA Methods: Using VBA you can insert a new row by referencing to a single cell, a range of cells or an entire row. Macro VBA - go to a row number specific in a cell - posted in Office: In a macro in Excel I want to refer to a cell where the user entered a number. Count, but it will act goofy if you don't select only a single column. VBA Select VBA Select CurrentRegion VBA ActiveCell VBA Selection Selection FillDown VBA EntireRow and EntireColumn VBA Rows and Columns VBA Row and Column VBA Select It is very common to find the. Last Non-Blank Value In A Row Or Column. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Excel VBA Last Row. Hope you can help. If you are learning to code only from the recorder. This example illustrates the End property of the Range object in Excel VBA. Row 'delete row from first until the last row Do Until xrow = lastrow If Cells(xrow, 1). Set rng = Range("A2"). In this video I explain the Range. You can set one or multiple criteria. It is time-consuming to scroll and go to the first or last empty row while you are working on a huge worksheet. When you set the Print Area (File>PageSetup>Sheet Tab), Excel creates a Name called Print_Area. End Method UsedRange. To select a single column you will write: Columns("A"). Hi, I would like to ask a question on range. Range("B4"). VBA Selection is used for selecting the range from excel worksheet. Next the same formula is changes but this time the Range reference is used. Note that there are some ways to determine the last row or column with data from a spreadsheet: Range("A1"). Arrays and ranges in Excel VBA - delete rows. Sub Select500Rows() Range(ActiveCell, ActiveCell. Delete a specific row. It is time-consuming to scroll and go to the first or last empty row while you are working on a huge worksheet. In order to understand the VBA, understanding of the basics of. Let me know in the comments section below how you use resizing a range in your macro code! Also, if you can think of any other ways to use VBA code to find the last row or last column, post your coding method in the comments section so we can improve the current list. Also see Last Value In Range, location of the last filled element within a range (either in a column or a row), Frank Kabel and Bob Phillips (based on Frank's posting 2004-05-31) The worksheet solutions above will work faster than a programming solution, but here is a comparable User Defined solution that may be of interest. Row 'Determines the last row with data from the first column Range("A1"). In any case you can use named range like normal way. Sort will sort all of the data starting at cell A5 and ending at column G in the last row with data. CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. With this Excel AutoFilter VBA sample code, you can display a message that shows a count of the rows that are visible after a filter has been applied: Sub CountVisRows() 'by Tom Ogilvy Dim rng As Range Set rng = ActiveSheet. It will allow you to delete rows in many different ways. Find the last used Cell on a Worksheet: Find & Return The Last Used Cell On An Excel Worksheet Or Column VBA Function Sub FindLastCell() Dim LastColumn As Integer Dim LastRow As Long Dim LastCell As Range If WorksheetFunction. Once we found that we can easily convert it as Range. You can select multiple ranges in single line code and perform the action you want to perform. Select Notice than Columns is plural even if you are selecting only one column. Excel is a perfect tool to store data that change every now and then. There are certain situations where we perform some tasks by finding last used Row with data in a Column. Arrays and ranges in Excel VBA - delete rows. How to find the last cell of the value and select the range like an example I have a data contains A1: A10, I have to fill the value in B1: B10 (it's empty cells). Choose Entire Row to delete all selected rows in your data. I tried "End(xlToRight). I want to select multiple rows (so I can then sort them) This is what I have so far: Do While Worksheets(I). EntireColumn. This article lists several ways to delete rows in Excel 2010-2013 based on a cell value. The VBA Range Object represents a cell or multiple cells in your Excel worksheet. Note If you try to record this procedure by using the macro recorder, you do not receive the same results. SpecialCells(xlCellTypeVisible). We want this ComboBox to show all the locations from a named range -- LocationList -- on the LookupLists worksheet. To get the last row number in a range, you can use a formula based on the ROW, ROWS, and MIN functions. 3 - Select only the visible data in column A (using. This post will explore some of the different strategies you can use. Benefit is that if you add cell between that named range, the named range adjusts automatically. The first macro does a normal copy and the second macro copy the Values. End(xlToRight). VBA Code - Sum Formula (Fixed Cell to Last Row) Hi All, I need to put a formula in my code that locates the last row in column S, which I have got, and then do a simple sum that sums up cells S2: bottom of the column (which can change in length). You can use the Rows method to refer to an entire row within a range. To get the last row number in a range, you can use a formula based on the ROW, ROWS, and MIN functions. I need to dynamically group rows together in an EXCEL worksheet. End(xlDown). UsedRange does not necessarily consider the active cell as used. It doesn’t appear to be any more complicated than that because you can create that Name manually and get the same effect. To do this, it is usually necessary to find the location of the last non-empty row. Let's say you have data in cells A1, B1, C1, D1, and E1 and you would like to select the last non-blank cell which would be cell E1 in the row. End Property The Range. The following code will select the last non-blank cell which would be E1 in this case, if A1 is the active. I know this should be very simple, but for some reason I cannot recall how to select last row in Excel. I need the VBA. Select returns cells A5:B5. Let’s say you have data in cells A1, B1, C1, D1, and E1 and you would like to select the last non-blank cell which would be cell E1 in the row. UsedRange Selecting The First Row of a Worksheet ActiveCell. The rows below the headers should contain the data you wish to filter; No blank rows can be in the data set (apart from the last row at the bottom) Criteria Range. To sort a range, you need to specify at least one key as well as the range to sort. There are certain situations where we perform some tasks by finding last used Row with data in a Column. Using VBA to perform some action on an Excel range is a frequent task, but to do so, you need a way to convey to VBA which cells you want to act upon. Range("C2"). Is it possible to find the last data cells in Range A to Q and then one row after that, delete the rest of rows. Just make sure what is returned is what you want - sorting by column A first might help with the results. This page shows examples of how you can speed things up a lot by using arrays. How To Store Current Range Selection using VBA in Excel? Now we can use any VBA code (add new sheets, select or hide cells etc) and then restore origin selection:. Select lastrow = ActiveCell. For examples, There may be many columns with data and each column may have different number of items (rows). Column 'Determines the last column with data from the first row Cells(1. Range(ActiveCell, ActiveCell. Select The Cells property returns an object that represents a specific row and column (individual cell) of a worksheet. Copy from row till the last row with data into one sheet using VBA in Microsoft Excel The macro will add a sheet with the name Master to your workbook and will copy the cells from every sheet in your workbook in this worksheet. CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. Range("B2"). Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet2") 'find first empty row in database iRow = ws. Difference between Cells and Range in Excel VBA Ma How to Select Entire Row/Column in Excel VBA Macro How to set the background color of cell using Exce Different Excel Cell Types in VBA Macros; How to count blank cells in given range in Excel u How to Sort and Move the worksheets using Excel Ma. Row Range("e2:e" & lastrow). A huge turning point for me when I was teaching myself how to write VBA was being able to create dynamic code. Select The Cells property returns an object that represents a specific row and column (individual cell) of a worksheet. Select Note: because we placed our command button on the first worksheet. The rows below the headers should contain the data you wish to filter; No blank rows can be in the data set (apart from the last row at the bottom) Criteria Range. Select InputRow = InputRow + 1 Loop But it just highlights the specific row everytime it goes through th loop and the one that was highlighted is not anymore. Go to Next Blank Row in Excel Posted on October 2, 2010 by andrew walker in Excel 2003 Help , Excel VBA , Macros with 11 Comments If you ever have to find the next available blank row in a worksheet you could try the following code below. Value = "" Then Cells(xrow, 1). UsedRange does not necessarily consider the active cell as used. The VBA Range Object represents a cell or multiple cells in your Excel worksheet. Never Use Select. To sort a range, you need to specify at least one key as well as the range to sort. Range("B2"). Not through RANGE object but also through CELLS property we can select the cell – CELLS is property in VBA. How To Transpose / Convert Multiple Rows Into Single Row without VBA and Array Formula If you are looking for a solution to Transpose / Convert Multiple Rows Into Single Row without VBA and Array Formula then you have landed on the right page. When you do so, the new formulas will return the first and last occurrences of Shoes and Socks, respectively. This blog post uncovers 6 different ways you can find the last row. This is unlike a range method where we used A1, A5, C5, C10 like references. For demonstration purpose we have applied the formula A1*B1 in cell D1. Select End Sub. But when you want to delete rows using more sophisticated methods you have to use VBA. By using Excel VBA range object, you can refer to, A single cell A row or a column of cells A selection of cells A 3-D range As we discussed in our previous tutorial, that. So the second line reads like ("A10:A1048576") unless you are using an older excel format. Find method which is the best method to use when your data contains. Excel VBA Range: How to Use Range in VBA October 18, 2013 by Kasia Mikoluk Every Excel user is familiar with selecting a range of cells - by SHIFT + Clicking, CTRL + Clicking, or dragging the mouse over a range of cells. This can be set up as a range of cells or. You can use array formulas to find the last non-blank cell in a column or row. SpecialCells(xlCellTypeVisible) 4 - This range now has the. Hi, I would like to ask a question on range. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. If you routinely have data that starts in A5:G5 but can be any number of rows tall, using Selection. Column E is the cell that the vlookup refers to. While working with excel everything refers to cells & ranges, it is not different in VBA as well. It is the most important object of Excel VBA. With some VBA, we can find the last cell on either a row basis or a. If you want to insert a single row by referencing to a range of cells you need to ensure that the. By using Excel VBA range object, you can refer to, A single cell A row or a column of cells A selection of cells A 3-D range As we discussed in our previous tutorial, that. I want to select 1, 2, 3, 4 from my spreadsheet and then delete the selection. Macro VBA - go to a row number specific in a cell - posted in Office: In a macro in Excel I want to refer to a cell where the user entered a number. VBA has no method to delete 'rows' in arrays. Range("A1:F" & Lastrow). This meant I didn't have to go into my macros and change range addresses in my code every time the size of my data changed (which was typically every day). Rows Method Range. Suppose we delete the last 4 rows, and press CTRL + END again. To find the last row in the used range with VBA, follow these steps: Identify the worksheet whose last row you want to find (Worksheet). Ask Question Asked 4 is there any other way i can set up last-row selecting range or what i am doing wrong in this. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. In this usage, Cells is used twice to determine a specific range of cells. 3 - Select only the visible data in column A (using. Are you ready? Place a command button on your worksheet and add the following code lines: 1. Select Note: because we placed our command button on the first worksheet. This method finds the desired range and then adds a row or column as directed by the value of the rowOrColumn parameter. Latest update on July 25, 2011 at 07:33 AM by jak58. How to find the last row of data using a Macro/VBA in Excel, including getting the number of that row and any data from that row. Range(Cells(StartRow, 1), Cells(EndRow, 1)) 'This is a one-column range set in which we search. VBA Lesson 2-6: VBA for Excel for the Cells, Rows and Columns. You can set one or multiple criteria. How to select last cell with data in a row/column in Excel? In Excel, you can use shortcuts—Ctrl + End to go to the last used cell of the active worksheet. The For loop has two forms: For Next and For Each In Next. This Excel UserForm has a ComboBox named cboLocation. Excel VBA Last Row. This will help you to know how to delete specific rows in a range from Excel workbook using VBA. The following code line selects the entire sheet. End (xlDown). These can be as simple as selecting a cell on an active worksheet, selecting one on another worksheet, or selecting a range of cells on a worksheet from a different workbook. Is there a VBA function to get the first and last row and column of a range? Bob · Without SpecialCells: Dim r As Range Set r. Using the Application's Selection property is. You'll note that we passed xlDown to the End property. Creating a named range on the fly is a very effective way of. End(xlDown). Select End Sub It is only selecting the first row for some reason and Im not sure what the issue is. Range("B4"). Delete a specific row. Excel VBA Delete Row: Step-by-Step Guide and 12 Code Examples to Delete Rows with Macros By J. End(xlToRight). In VBA macros, you can use the CurrentRegion property to expand a selection to include the current region. How To Store Current Range Selection using VBA in Excel? Now we can use any VBA code (add new sheets, select or hide cells etc) and then restore origin selection:. This macro prompts you to select a date range then copies the rows in that range to an already-existing worksheet - copy_date_range_data_to_existing_worksheet. Take Your Next Steps. In this VBA excel automation ,we had data in column A and column B in 10 rows. Many, who start using VBA in Excel, quickly learn how to use ranges, because they are really handy and relatively easy to understand and use. To sort a range, you need to specify at least one key as well as the range to sort. This page shows examples of how you can speed things up a lot by using arrays. I’ll apply an approach related to that in my VBA Conditional Chart Formatting series: VBA Conditional Formatting of Charts by Series Name, VBA Conditional Formatting of Charts by Category Label, and VBA Conditional Formatting of Charts by Value, with a little help from Referencing Pivot Table Ranges in VBA. This could be deleting rows in a range (table) or other operations. Range("C2", ActiveSheet. CurrentRegion. Select Note: because we placed our command button on the first worksheet. Is there a VBA function to get the first and last row and column of a range? Bob · Without SpecialCells: Dim r As Range Set r. Value <> "" Rows(InputRow). How to Select Dynamic Used Range in Excel VBA VBA Macro to Select the Dynamic Actual UsedRange in Excel Generally in most of the cases the Used Range is always dynamic. Today I am going to take on one of the most frequent question people ask about Excel VBA - how to the the last row, column or cell of a spreadsheet using VBA. End(xlToRight). End (xlDown). Is there a VBA function to get the first and last row and column of a range? Bob · Without SpecialCells: Dim r As Range Set r. When specifying a range of cells with offset, not only does the first cell (A1 above) get moved across and down 1 but the last cell (C3) gets moved across 1 and down 1. select", but if an item is added "A68" would no longer be the last empty cell/row. This article will show you how to use Range, Cells, Offset and Resize to select a range in Excel VBA. No guarantees are given for any later versions of Word,. The macro will then check every cell within your specified range and create a new range whose cells are within the minimum and maximum values specified. The macro's give you the row or column number of the last cell with data in one row or one column. Translated by jak58. VBA is probably harder than VB. I’ll use arbitrary data and an. To reset the used range, we need to resort to using VBA. Sub Last_Row_Range() Dim Addme As Range. Select for a single cell and Range("A1:C5"). Cells(InputRow, 1). Select Selection. Fine, so what are some reliable methods for finding the last used cell in an Excel worksheet or a range? It depends on what you mean by the last cell? The following code sample show my ideas for how to tackle the following scenarios: Find the last cell that contains data in a row or column; Find the last cell that contains data in a range. Introduction. Select End Sub Select and Copy 500 cells using VBA. Choose Entire Row to delete all selected rows in your data. - the last cell that was populated (but isn't now) is selected. To insert a row use the Insert method. This page describes formulas to find the last non-blank element in a row or column. Column E is the cell that the vlookup refers to. Word VBA: Adding a Row Below the Last Row of a Selected Table by Selection Object June 20, 2015 This Word VBA macro adds a row below the last row of a selected table. CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. In this VBA excel automation ,we had data in column A and column B in 10 rows. Copy to a Database sheet with VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Svetlana has a worksheet with filtered data and wants to know how to delete visible filtered rows and leave hidden rows intact. Note: instead of Range("A5"), you can also use Range("A1"), Range("A2"), etc. Excel VBA Last Row. Difference between Cells and Range in Excel VBA Ma How to Select Entire Row/Column in Excel VBA Macro How to set the background color of cell using Exce Different Excel Cell Types in VBA Macros; How to count blank cells in given range in Excel u How to Sort and Move the worksheets using Excel Ma. Select method is used by VBA Macro developers for various purposes. Determines the range of cells that comprises the last filled row across as many columns Discussion: If you have a varying range of data that you need to work with and it is always appearing in the last row, and it is unknown how many columns across it may take up, this option offers a method to determine the range. VBA Last Cell Filled. Row, "FF" & r). Dynamic range as RowSource. NET because you have to know both VB and Excel VBA objects. This article will show you how to use Range, Cells, Offset and Resize to select a range in Excel VBA. Attempt to reset the used range of Worksheet (Worksheet. Now, we aim to copy the formula till last populated row, which is row 10. Im using this bit of code which I thought would select Column A to FF and go to the last row containing data in this range. TYPING THE MACRO. Select the Last Non-Blank Cell in a Row. Column 'Determines the last column with data from the first row Cells(1. How to set up lastrow selecting range in vba. I added in some extra lines to find the first cell that has data in it, and wrote a function to find the "Actual" used range in a sheet. In most of the cases, you are better off not selecting cells or ranges (as we will see). Range("B2"). There is no way to change the way that SpecialCells(xlCellTypeLastCell) determines the last cell. Column E is the cell that the vlookup refers to. Sub RangeSelect() ActiveSheet. 7/14 Completed!. I know that I can type Selection. This function accepts a worksheet object as an argument and returns the Actual Used Range. VBA Find Last Used Row with data in particular Column – Excel Macros Examples Codes helps in automation. Join Date Feb 2010 Location London, UK Posts 9,306 Post Thanks / Like Thanks (Given) 54 Thanks (Received) 163 Likes (Given) 206 Likes (Received) 150 Mentioned. 6 KB) To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell. Count to Rows. You don’t need to know the number of columns you will need, just select more columns than rows to indicate you want the result across a single row. I know how to do this in excel, but access is proving ot be quite different. VBA Last Cell Filled. This can be for many reasons such as to make a chart or PivotTable ranges dynamic, or to append fresh data to the bottom of a list. How to delete alternate rows with VBA; How to delete every Nth row in Excel; How to delete every other row in Excel by filtering. I want to select 1, 2, 3, 4 from my spreadsheet and then delete the selection. Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet2") 'find first empty row in database iRow = ws. Note: instead of Range("A5"), you can also use Range("A1"), Range("A2"), etc. You can set one or multiple criteria. This article will show you how to use Range, Cells, Offset and Resize to select a range in Excel VBA. The Complete Guide to Ranges and Cells in Excel VBA by Paul Kelly · 150 Comments “It is a capital mistake to theorize before one has data”- Sir Arthur Conan Doyle. How you are using it like Property/Method of a range ? I think you are adding row not coumn. Cell and Range VBA properties and actions Posted on November 30, 2016 December 28, 2018 by Excel Off The Grid Manipulating ranges and cells is one of the most common actions in VBA. Arrays and ranges in Excel VBA - delete rows. Select a range in a VBA macro from A1 to last row/column cell. You could then use the last row variable in the macro in place of the row number, for example. Select 500 Rows using VBA. This is unlike a range method where we used A1, A5, C5, C10 like references. A very common task in Excel VBA is to find the last row of a list. And the part is, these codes can highlight cell using different ways. When you use the following: Range("A2:L2" & Lastrow). Power Excel vba secret, avoid using select I see it all the time, code that selects one thing, then another, then selects something else in order to navigate and write data in an Excel spreadsheet. You can use array formulas to find the last non-blank cell in a column or row. The criteria range are the rules that will be applied to the data when using the VBA Advanced Filter. End (xlDown). My spreadsheet has 18 columns and (up to) 5,000 rows of data. Range(ActiveCell, ActiveCell. Join Date Feb 2010 Location London, UK Posts 9,306 Post Thanks / Like Thanks (Given) 54 Thanks (Received) 163 Likes (Given) 206 Likes (Received) 150 Mentioned. Svetlana has a worksheet with filtered data and wants to know how to delete visible filtered rows and leave hidden rows intact. Introduction. The UsedRange represents a Range used by an Excel Worksheet. Moving around the Worksheet in VBA for Excel Rows and Columns in VBA for Excel Columns and Column. When you set the Print Area (File>PageSetup>Sheet Tab), Excel creates a Name called Print_Area. Offset(4, 3). Can some one please point to where my mistake is with the Range.