Find the last not empty row in a range of cells holding a formula
excel vba find last row with data in range
excel go to last non-empty cell in column
excel formula to find last non zero value in a column
excel find last row with specific value
excel get address of last non-empty cell
excel find last column with data
excel address of last cell with data
How can I find the last row in a range of cells that hold a formula, where the result of the formula is an actual value and not empty?
Say in a simplified way that the range of cells
("E1:E10") hold a formula referring to cells A1 through A10 as followed
=IF("A1"="","","A1"). But only the cells A1 through A6 have a value filled in, so the result of the formula for cells E7 through E10 will be empty.
Trying to do it with:
lastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
results in lastRow having the value of
10. What I want is for the value of lastRow to be
6 in this example.
The actual code is way more complex than this so I can't just check for the last filled in Row of Column A, as the formulas refer to single cells on different sheets and are added dynamically.
I think that more elegant way than was provided by
@D_Bester is to use
find() option without looping through the range of cells:
Sub test() Dim cl As Range, i& Set cl = Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row) i = cl.Find("*", , xlValues, , xlByRows, xlPrevious).Row Debug.Print "Last row with data: " & i End Sub
Also, more shorter version of the code which was provided above is:
Sub test2() Debug.Print [E:E].Find("*", , xlValues, , xlByRows, xlPrevious).Row End Sub
Find the last not empty row in a range of cells holding a formula, I think that more elegant way than was provided by @D_Bester is to use find() option without looping through the range of cells: Sub test() Dim cl As Range, To find the value of the last non-empty cell in a row or column, you can use the LOOKUP function in this surprisingly compact formula. As an additional bonus, this formula is not an array formula, and not volatile.
You want to find the last cell in a column that is not empty AND is not a blank string("").
Just follow the LastRow with a loop checking for a non-blank cell.
lastrow = ActiveSheet.Range("E" & ActiveSheet.Rows.Count).End(xlUp).Row Do If ActiveSheet.Cells(lastrow, 5).Value <> "" Then Exit Do End If lastrow = lastrow - 1 Loop While lastrow > 0 If lastrow > 0 Then Debug.Print "Last row with data: " & lastrow Else Debug.Print "No data in the column" End If
Notice that your
Rows.count does not specify which sheet. That means it will use the active sheet. Of course
ActiveSheet.Range() also is on the active sheet. But it is bad practice to mix
.Rows. It indicates a thoughtless usage that could bite you if you changed the
ActiveSheet but didn't change the unspecified reference.
Excel formula: Get value of last non-empty cell, To find the value of the last non-empty cell in a row or column, you can use the As an additional bonus, this formula is not an array formula, and not volatile. Here we feed the row numbers of the same range into lookup for the result vector In this example, we will look at a method for returning the last non-empty row in a column for a data set. This method will work regardless of empty regions within the data set. However caution should be used if merged cells are involved , as the End method will be "stopped" against a merged region, returning the first cell of the merged region.
This should help you determine the last row containing a formula (in column
A on sheet1
lastRow = Split(Split(Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas).Address, ",")(UBound(Split(Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas).Address, ","))), "$")(2)
SpecialCells is used to determine the range of all the cells containing a formula. This range is then parsed using
Ubound the last of these cells is being retrieved. The result is being split again to extract the row number.
Count number of blank cells till last non empty cell in a column , When I use Countblank formula, it takes an argument as range which should be However, I thought of following but failed on below also, just see if you have Now, with above formula I am getting the last non empty row number of a column. Please note that it is an array formula, so it must be entered with holding Assuming that you have a list of data in range A1:A5, and you want to find the first non-blank cell in a given range of cells in Excel. How to do it. You can use a formula based on the INDEX function and the MATCH function to get the first non-blank value in a one-column range in Excel.
Function returnLastRow() With ActiveSheet.UsedRange returnLastRow = .Find("*", , xlValues, , xlByRows, xlPrevious).Row End With End Function
Get the Row number of the last non blank cell in a column, Get the Row number of the last non blank cell in a column in Microsoft Excel devise an excel formula for last row which will find out the cell number of the last non blank cell COUNTA: Counts the number of cells in a range that is not empty. Cells(Rows.Count, 1): This part selects cell in column A. Rows.Count gives 1048576, which is usually the last row in excel sheet. Cells(1048576, 1) End(xlUp): End is an method of range class which is used to navigate in sheets to ends. xlUp is the variable that tells the direction.
VBA Last Row, As we would not prefer to update the cell ranges every now and then when we are Step 2: Use the defined variable to hold the value of the last non-empty row. Code: the column A under Range function while defining the variable formula. I am trying to find the last row of a column. Every cell in the column has the formula =IF(ISERROR(AVERAGE(F5:G5));"";AVERAGE(F5:G5)) I am using the below VBA code to find the last row.
Strategies for getting the last row in Excel with VBA, Using VBA to get the last row in Excel can be a surprisingly complex task. scheme for the cells in each worksheet (instead of holding a gigantic For name ranges they skip hidden rows but stop at the row before an empty cell.: Find. Using Find on Formulas includes hidden rows, whereas using Find on How can I find the last row in a range of cells that hold a formula, where the result of the formula is an actual value and not empty? Say in a simplified way that the range of cells ("E1:E10") hold a formula referring to cells A1 through A10 as followed =IF("A1"="","","A1"). But only the cells A1 through A6 have a value filled in, so the
Excel Cells & Ranges - Selecting Cells, If using the notation Range("An") is not convenient you can use Cells(rows,columns) instead. GoTo is a method that causes Excel to select a range of cells and activate the corresponding The UsedRange is the range of all non-empty cells. The default is to select all constants or formulas, no matter what the type. End (Example1) can be used to find out the first blank cell/row, or last non-empty cell/row in a given column using VBA code. The end works on single column most of the time. If you have data in ranges, it would be difficult to decide which column should be used to find out the last non-empty row.
- Works great and was more what I was looking for. I just have one question, what is the meaning of the ampersand
&in the declaration of the variable
i? (As it seems to work just the same without the ampersand)
- @Pascale it means
Dim i as Long, if you will remove
iwill be declared
as Variant. It just a shorthand method of coding, e.g.
Double, if not specified then
- @Vasily, thanks for giving me the whole list of shorthand declarations and -1 to Micrososft for not including them in their general Declaring Variables page
- This answer is elegant! Россия красиво
- I had already thought about looping through them backwards like you suggest, but was hoping for a more elegant way to achieve it.
- Thank you for your answer and thank you for explaining how to improve the use of Rows.Count and ActiveSheet.
- I think there is, try using the "divide and conquer" approach. As
2^20is equal to 1048576. That is the value of
Rows.Count. That said your
Do...Loopwill never repeat more than 20 times to reach the
- @D_Bester there is another option to find the last non blank cell using
find()option, withot looping through the range
- I tried to apply your code but it returns the first row having a formula in it. (The range in my sheet is from row 3 to row 26 and your lastRow returns 3: when I print it out with MsgBox, when I expected it to be 21) Not sure what i need to adjust or even if this is a good approach. You say "This should help you determine the last row containing a formula" but i need the last row containing a formula where the result of that formula is not "" (empty string). Sorry if I misunderstood your answer but I have no experience with vba at all so I am totally relying on what i find on stackoverflow.
- @Ralph The Address property is limited to a 255-character string, and so may not include all of the areas containing formulas.