-2

I am trying to find the last row number in a column in a specific VBA Excel sheet. The row number needs to be put into a var for later use. Thanks in advance!

Dim i As Integer
Dim x As Integer
Dim lRow As Integer
Dim subj As String

subj = TextBox1.Text
lRow = ThisWorkbook.Sheets("Data").Range("A").Rows.Lastrow

ThisWorkbook.Sheets("Data").Cells(lRow, 1) = subj

For i = 1 To 1000

    If ThisWorkbook.Sheets("Data").Cells(1, i) = ThisWorkbook.Sheets(subj).Cells(1, 1) Then

    End If

Next i
Community
  • 1
  • 1
SaladSnake
  • 167
  • 2
  • 2
  • 15
  • This is unlikely to be covered in an answer here in as a good a detail as it should, unless as a community wiki, IMHO. Please see the following as an example of why it all depends on your data: https://www.rondebruin.nl/win/s9/win005.htm That said, what should be mentioned is do not use Integer with last row as you risk overflow. Use Long. – QHarr Apr 20 '18 at 05:17
  • `Integers` are stored as `Long` anyway, but they still have limits. So use `Long` all along. – Michał Turczyn Apr 20 '18 at 05:19
  • And for more info......https://stackoverflow.com/questions/26717148/integer-vs-long-confusion – QHarr Apr 20 '18 at 05:27
  • Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) – AntiDrondert Apr 20 '18 at 06:50

2 Answers2

3

If there are no data gaps then

lRow = ThisWorkbook.Sheets("Data").Range("A1").end(xldown).row

Else if there are gaps and you want the absolute last then

lRow = ThisWorkbook.Sheets("Data").Range("A1048576").end(xlup).row
Raunak Thomas
  • 1,393
  • 1
  • 12
  • 28
1

I will try elaborate already given answer.

To find last row/column, there's End method of a Range object. I.e. if you have Cells(row, column), you then can append to it .End() which can have four arguments: xlUp, xlDown, xlToLeft, xlToRight. This function returns a cell (so Range object), so to have row/column number, you need to acces Row / Column property. To sum up, you should use:

Cells(row, column).End(X).Row
Cells(row, column).End(X).Column

where row, column are integers and X is one of mentioned parameters.

To find last row, you should use specific column number (for column that you want to know last row). You have two options there:

  1. xlDown - you can think of it like this: start in a given cell, then go down until first blank cell is met. Return last non-empty cell.

  2. xlUp - start in given cell, then go all the way up until non-blank cell is met.

Note: if you start with empty cell, End method will return first non-empty cell. On the other hand, if you start with non-empty cell, the End method will go until first empty cell is met and return last non-empty cell.

It all sound complicated, but let me give some examples:

let's have a sheet:

  A
1 1
2 2
3
4 4
5 5

then

MsgBox Cells(1, 1).End(xlDown).Row 'returns 2
MsgBox Cells(5, 1).End(xlUp).Row 'returns 4
'start with cell in last row
MsgBox Cells(Rows.Count, 1).End(xlUp).Row 'returns 5
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69