FINDING THE LAST VALUE IN COL D WITH BLANK ROWS BETWEEN VALUES

[chazrab]

As described in the title.
This code does not work even if col D is specified as below. Cols A, B and C all have non-blank rows down to row 47.

With Sheets("BIBLESTUDYALL")
    lastRow = .Cells(.rows.count, "D").End(xlUp).Row
End With
MsgBox  lastRow

MsgBox always keeps giving 47 as LastRow - the last used row in cols A, B and C. Not what I want. The value should be 10.
as image below shows

What am I not seeing? - this couldn't be simpler.

Please help if you can.

Thank as always,
cr

 

[MARK858]

Are there formulas in the cells returning ""
Edit: my bad you are looking for the value in the cell, see post 3 by dsfobral

 

[dfsobral]

didn't you defined lastrow as the number of the row?

shouldn't you use
lastRow = .Cells(.rows.count, "D").End(xlUp).Value

or simply
lastRow = .Cells(.rows.count, "D").End(xlUp)

 

[chazrab]

didn't you defined lastrow as the number of the row?

shouldn't you use
lastRow = .Cells(.rows.count, "D").End(xlUp).Value

or simply
lastRow = .Cells(.rows.count, "D").End(xlUp)

Are there formulas in the cells returning ""
Edit: my bad you are looking for the value in the cell, see post 3 by dsfobral

Hi MARK,
Didn't know if you are still following the thread, so I copied you also on my reply:
....
I copied your first line of code above as below and just inserted between the With End With block.
I gives a 'Type Mismatch error at this line. Her's the revised code block with your code line substituted:

Private Sub cmdFINDBKMARK_Click()
Dim LastRow As Long
With Sheets("BIBLESTUDYALL")
  LastRow = .Cells(.rows.count, "D").End(xlUp).Value  ---> 'Type Mismatch error here
End With
MsgBox LastRow
End Sub

To me this is beyond easy - but it's got me stumped.
Thx , cr

 

[MARK858]

I ran it with the data below and got no type mismatch

Are you sure that the cells below the cell with 10 in it are actually blank cells i.e. no formulas / characters (including spaces) in the cells

 

[chazrab]

I ran it with the data below and got no type mismatch
View attachment 112022
Are you sure that the cells below the cell with 10 in it are actually blank cells i.e. no formulas / characters (including spaces) in the cells

Hi MARK,
Didn't know if you are still following the thread, so I copied you also on my reply:
....
I copied your first line of code above as below and just inserted between the With End With block.
I gives a 'Type Mismatch error at this line. Her's the revised code block with your code line substituted:

Private Sub cmdFINDBKMARK_Click()
Dim LastRow As Long
With Sheets("BIBLESTUDYALL")
  LastRow = .Cells(.rows.count, "D").End(xlUp).Value  ---> 'Type Mismatch error here
End With
MsgBox LastRow
End Sub

To me this is beyond easy - but it's got me stumped.
Thx , cr

 

[chazrab]

didn't you defined lastrow as the number of the row?

shouldn't you use
lastRow = .Cells(.rows.count, "D").End(xlUp).Value

or simply
lastRow = .Cells(.rows.count, "D").End(xlUp)

I used Clearcontents on the entire column, inserted numbers on random rows with blank cells between
values, reran the code twice and it gave me the correct last value both times. I must have unknowingly had data or formulas
in the cells below the last value in the column. Thanks for all your help.

cr

 

[MARK858]

Happy we could help

If it was formulas returning "" below the cell with 10 in it causing the issue (the cells in between are irrelevant) then you could use the code below (btw I didn't post the previous code, dsfobral did)

Private Sub cmdFINDBKMARK_Click()
    Dim LastRow As Long
    With Sheets("BIBLESTUDYALL")
        LastRow = .Columns(4).Find("*", , xlValues, , xlByRows, xlPrevious) 
    End With
    MsgBox LastRow
End Sub