Finding the last row that contains a value, of a column

[riedyp]

Hello,
I am trying to select a cell in the last row that does not contain a 0. It is taking me to Row 54 but I want it to go to Row 37. The method I know is to go to a cell at the bottom of the sheet and "xlUp" so it will find the first value it finds. I want to do this but want to ignore all of the "0's" till it arrives at value "3.3" in Row 37.
Any method that would solve this would be greatly appreciated.

Private Sub UpdateButton_Click()
NewRow = ActiveSheet.Cells(1000, 3).End(xlUp).Row 'Goes to the last updated cell
ActiveSheet.Cells(NewRow, 3).Select
End Sub

 

[jasonb75]

The only ways that I can think of to do that are to loop backwards

Sub riedyp1()
Dim lRow As Long, newRow As Long
lRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
For newRow = lRow To 1 Step -1
    If ActiveSheet.Cells(newRow, 3) <> 0 Then Exit For
Next
ActiveSheet.Cells(newRow, 3).Select
End Sub

or to apply a temporary filter.

Sub riedyp2()
    With ActiveSheet.Range("$C$23")
        .AutoFilter Field:=1, Criteria1:=">0", Operator:=xlFilterValues
        .EntireColumn.SpecialCells(xlVisible).Cells(Rows.Count).End(xlUp).Select
        .AutoFilter
    End With
End Sub

 

[riedyp]

Thank you for the help. I forgot to mention that it is being assigned to a button. Is there a way for this to run through all of those iterations with just one click? Because as of now if I click once it Selects C54 then another click C53 and so on.

 

[jasonb75]

What else are you trying to do with the code?

For me it goes straight to C37, assigning it to a button shouldn't change that.

 

[riedyp]

All I changed was putting the code within my button. So did I do this wrong?

Private Sub UpdateButton_Click()
Dim lRow As Long, newRow As Long
lRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
For newRow = lRow To 1 Step -1
    If ActiveSheet.Cells(newRow, 3) <> 0 Then Exit For
Next
ActiveSheet.Cells(newRow, 3).Select
End Sub

 

[Fluff]

Are the "0" values actually 0 or something like 0.001?

 

[riedyp]

The list is all cells that reference another column. The "0's" are the cells that do not contain a value at the moment. I am doing this to have a button to update my list to the correct amount of values that will be formatted.

 

[jasonb75]

The list is all cells that reference another column. The "0's" are the cells that do not contain a value at the moment.

In that case why not find the last row from the other column?

 

[riedyp]

I think I eliminated that from my thought process because I dont know how to:
find the last row
select a range of cells from columns E,H,I,J of the "LastRow" to row 9
then put those values at row 24.

would you have a better idea of how to do this? I am doing this to create a dynamic table

 

[jasonb75]

So you're just copying down from above?

If it is a direct copy then you could simply use a very simple formula and do away with the code and button.

Assuming that your existing formula in C24 is =E9 all you need to do is change it to =IF(E9="","",E9)

Somehow I suspect that it will not be that simple, but asking half of a question usually results in half of an answer.