-2

enter image description here

Hello guys, as you can see the column B has formulas that are conected to the column E. And I would like to find the last row in column B that has a value. In this case it would be row 5 and not row 25. I used this code: LR = Cells(Rows.Count, 1).End(xlUp) But its not what I want. Because there are formulas un column B i can´t find the last row which has a value.

  |    A     |    B     |    C     |    D     |    E     |
 1|Date      |Week      |Product   |Name      |date      |
 2|          |        36|          |          |12.09.2021|
 3|          |        37|          |          |13.09.2021|
 4|          |        43|          |          |25.10.2021|
 5|          |         4|          |          |30.01.2021|
 6|          |          |          |          |          |

25|          |          |          |          |          |

Color fill in A2 .. P25 yellow

Formula in column B5, copies of which are repeated from row 2 to row 25
=IF(E5="";"";TRUNC((E5-DATE(YEAR(E5+3-MOD(E5-2;7));1;MOD(E5-2;7)-9))/7))
John Alexiou
  • 28,472
  • 11
  • 77
  • 133
Luís Melo
  • 1
  • I cant see the image, but i tried something like so `range("b1:b9").SpecialCells(xlCellTypeFormulas,2).row-1` on a simple formula, can you show the image in text in the post, not all can access images from sites at work. Thanks – Nathan_Sav Sep 16 '21 at 10:24
  • Week Product Name Date 36 12.09.2021 37 13.09.2021 43 25.10.2021 4 30.01.2021 – Luís Melo Sep 16 '21 at 10:47
  • Luis, for eventual future needs, to get help from those that can not view images, you can provide something like I did for you above. – Tom Brunberg Sep 16 '21 at 11:54
  • 1
    Does this answer your question? [Row count where data exists](https://stackoverflow.com/questions/18088729/row-count-where-data-exists) – John Alexiou Sep 16 '21 at 11:54

1 Answers1

0

If you want to go the formula route, try =MAX(IF(B:B="",0,ROW(B:B))).

Note, this formula could be slow if you are using it a lot.

If you want to use VBA, you could try (from https://stackoverflow.com/a/51559438/9393094),

Function GetLastRow(col, row)
    ' col and row are where we will start.
    ' We will find the last row for the given column.
    Do Until ActiveSheet.Cells(row, col) = ""
        row = row + 1
    Loop
    GetLastRow = row
End Function
Dave Thunes
  • 260
  • 2
  • 9