Value of last non empty cell-CodePudding

I'm using the following formula in Excel:

LOOKUP(2;1/(A:A<>"");ROW(A:A))

To find the position of the last non empty cell using this tutorial https://exceljet.net/formula/get-value-of-last-non-empty-cell

However, I would like to use it in VBA however I cannot create a similar code.

nextrow = Application.WorksheetFunction.Lookup(2, 1 / (Range("A:A") <> ""), Range("A:A").Row)

But it's not working, I get a type mismatch. How could it be achieved?

CodePudding user response：

 Set sh = ActiveSheet ' or whatever sheet you are working on
 nextrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row   1

CodePudding user response：

I think that standard way wouldn't be using of worksheet function. For me it would utilising the way I would find last non empty cell manually in Excel - CTRL arrow down.

In case the last non empty cell is also first one:

Sub Find_first_non_empty_row()

Dim Last_value As String


'select first cell in the range with values
Range("A1").Select

'go to the end to the last cell with value (same as CTR   arrow down) and select the cell
Selection.End(xlDown).Select

'get value of the selected cell
Last_value = Selection.Value

But in the link you shared I see that there are some empty rows splitting the ranges with values - so the first non-empty cell is not necessarily the last. In that case one lecturer on Udemy was suggesting starting at the end and going up:

Sub Find_last_non_empty_row()

Dim Last_value As String


'select lasst cell in the sheet
Range("A1045876").Select

'go to the first cell with value up (same as CTR   arrow up) and select the cell
Selection.End(xlUp).Select

'get value of the selected cell
Last_value = Selection.Value

CodePudding user response：

This is the standard way of fining the last used cell in a Column A.

Cells(Rows.Count, "A").End (xlUp)

But it returns the last visible cell in Column A.

Range.Find() will return the actual last non-empty cell in a range or Nothing if all the cells are empty.

Columns("A").Find(What:="*", _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False)