Use of Macro to FindLastRow (based on values) and Move Right to first cell for Entry

[SeanBH]

Hi All! First time poster here...I recorded a macro that finds the last row and selects the 9th column cell on the next row down (and have it running upon opening the workbook). Works fine right now, but I did notice that the LastRow value is not dynamic, so I need to make an adjustment to allow it to be so, as I add data to this sheet every day. (The macro is at the bottom of the image). Is there better VBA code (offset from LastRow?) that will accomplish this? I also want to add the same code to another sheet that is much different dimensionally. This one shown below is currently 40815 rows and 53 columns, and will only represent a years' worth of information.

Please let me know! Thanks!

 

[MARK858]

First of all welcome to the board.
When posting code can you please paste it directly in the thread in code tags (paste the code, select the code and click the

icon), as what you posted is difficult to read and impossible to copy.

Does the code below do what you want?

Sub Macro1()
  Dim Lr As Long
  Lr = Columns(1).Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
  Cells(Lr + 1, "J").Select
End Sub

By the way in the find code you posted xlByColumns would in most cases give you an incorrect answer (as would xlNext).

 

[SeanBH]

Hi Mark858...thanks! Yes...your code worked for me, and thanks for the tip on pasting the code- this is the first time I've ever posted in a forum about anything software or coding!

I did manage to get my code to work better by changing the last line to "ActiveCell.Offset(0, 9).Range("A1").Select" and adding the Sheets.[SheetName].Select line at the beginning, like this (practicing your tip!:

Sub FindLastRow()
'
' FindLastRow Macro- 1st attempt
'
    Sheets("Solar Production By Hour").Select
    Range("A11").Select
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 9).Range("A1").Select

End Sub

It seemed to work just fine, but I think your code actually runs a bit quicker, so thanks again! I wish I knew more about how you arrived at your code, but we can leave that for another day. My First attempt was just letting the Macro record it for me.

 

[MARK858]

You're welcome

Just a few pointers on the last code you have posted.

1) because you are using xlNext you are looking from the Activecell (wherever that is, in this case A11) down the sheet until you find a cell with an empty string, so if you have data below the empty string cell then it won't give you the actual last row because it will stop at the empty string cell (the code I used looks from the bottom of the sheet upwards until it finds a cell with data in it).

2) you are using Cells which is looking at every column whereas you only need to be looking at column A, which is why I used Columns(1), or you could use Range("A:A") or Columns("A").

3) You correctly state that referring to the cell directly is faster than using the offset but it is such a small difference that you wouldn't notice it in this case (or most other cases for that matter).
What you notice causing your code to be slower is probably just because you using an extra Activate.

The above are only pointers, use what works best for you as there isn't a correct way of coding

 

[SeanBH]

string cell (the code I used looks from the bottom of the sheet upwards until it finds a cell with data in it).

You're welcome

Just a few pointers on the last code you have posted.

1) because you are using xlNext you are looking from the Activecell (wherever that is, in this case A11) down the sheet until you find a cell with an empty string, so if you have data below the empty string cell then it won't give you the actual last row because it will stop at the empty string cell (the code I used looks from the bottom of the sheet upwards until it finds a cell with data in it).

I really appreciate the help Mark858! On the first point, I am aware of the issue with holes in consecutive data, which is why I constructed the data the way I did so the only empties would be at the bottom. But I completely understand the warning! And I really appreciate knowing a better way to do it. I did utilize your code for the same purpose on another worksheet, and it is working well also. Again, thank you for your help!