CODEWITHSUNDEEP
vbaexcelexcel-2007
user1560170
user1560170

Reputation: 11

Get row index of last non-blank cell.

I'm new to coding macros and just had a quick question. What I have been trying to do is select all the data points before an empty cell, then store the row index of the last point. For example, in the code below I would select rows 1-4 and the row index that would be stored is 4. I have this code so far which selects the data points:

Cells(2, 2).Select
Range(Selection, Selection.End(xlDown)).Select

I just need to store the last row index. Example data:

1. 342
2. 342
3. 324
4. 234
5. <This would be an empty cell>
6. 43242
7. 342
8. 32423
9. 4324

Upvotes: 1

Views: 6779

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

Try this

LastRow = Cells(2, 2).End(xlDown).Row

If you are intent on Selecting the range, use

LastRow = Selection.Row + Selection.Rows.Count - 1

Although I would advise against Selecting ranges. Use this instead 

Dim rng As Range
Set rng = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
LastRow = rng.Row + rng.Rows.Count - 1

Upvotes: 1

Imaginativeone
Imaginativeone

Reputation: 107

' column = whatever column you're working with

For evalRows = 1 to Range(Selection, Selection.End(xlDown)).Row

  If IsEmpty(Cells(evalRows, column).Value) Then

    ' you can only refer to the previous row if you're not on the first row
    If evalRows = 1 then

      ' do nothing

    Else

      ' refer to previous row
      lastUsefulRow = Offset(evalRows - 1, column).Row

    End If

  End If

Next

Upvotes: 0

Related Questions