0

I'm trying to find the last row, that contains a value in a certain column (S) in my worksheet wsCopy. I am using this VBA code:

LastRow = wsCopy.Cells(wsCopy.Rows.Count, "S").End(xlUp).Row

However, it returns the last row with a formula (which is always further down), not the last row that contains actually a value. How could I change my code? I know there is a HasFormula property, I just don't know how to use it here. Thanks for your help

Martin Graupner
  • 103
  • 1
  • 2
  • 8
  • Thank you for your advice! I like the first part of it. How am I using the second command? ```LastRow = rng(rng.Cells.Count).Address``` ? – Martin Graupner Aug 30 '22 at 16:58
  • 2
    the `Find()` from the dup works if you only look at the column you want and us `xlValues` instead of Formula. @JvdV – Scott Craner Aug 30 '22 at 17:15
  • @JvdV yes but it will ignore `""` returned by formula. Which is what I am assuming the OP wants. – Scott Craner Aug 30 '22 at 17:17
  • 1
    @JvdV as per my edit. I assume the OP has a column full of formula that return a value or an empty string (`""`). Using `xlValue` will return the last row where the formula returns a sound value. – Scott Craner Aug 30 '22 at 17:20
  • @ScottCraner Thank you - your assumption is absolutely right! I have a column full of formulas (well, at least about 100) that return either a value or an empty string. So I am going to try the proposed method now - sorry for being slow JvdV thank you. Not fully sure what you mean but basically it's all formulas and some return values, some return empty strings – Martin Graupner Aug 30 '22 at 17:30
  • 1
    @MartinGraupner, glad that is cleared then! One could read your question two-fold and I just happen to read it wrong. – JvdV Aug 30 '22 at 17:31
  • I am just wondering: do I need the whole section of "Finding last row of a Table" in the dup? I want to use only the part ```lastrow = .Find(What:=.....).Row``` but I need to enter the column as well... EDIT: nevermind, it is the section Find Last row in a Sheet! sorry – Martin Graupner Aug 30 '22 at 17:42
  • First, `Set rng = wsCopy.Columns("S").SpecialCells(xlCellTypeConstants)`. Second, `a = Split(rng.Areas(rng.Areas.Count).Address, ":")`. Third, `Debug.Print "Last row with Const at " & Range(a(UBound(a))).Row`. – Алексей Р Aug 30 '22 at 18:06
  • @JvdV thanks everyone. When I am trying to run the code, it throws me a 'Runtime error 9: Subscript out of range' right at the beginning ```With Sheets(wsCopy.Name)``` . Apparently the sheet name doesn't exist despite I am basically giving it exactly that property. Still trying to figure that out... – Martin Graupner Aug 30 '22 at 19:51
  • @АлексейР I tried it it is at least running and throws no error! However, it seems to find only the first row all the time. On top of that, it also selects the header as well. I did: ```lastrow = Range(a(UBound(a))).Row``` – Martin Graupner Aug 30 '22 at 20:01
  • FYI: The .Find() method always finds the first cell (with values) not the last. So far I wasn't able to solve the problem. – Martin Graupner Aug 31 '22 at 15:44

0 Answers0