CODEWITHSUNDEEP
vbaexcel
DL1
DL1

Reputation: 201

lastrow and excel table.

I have tried to define a lastrow code that would return the last row value of the last non empty cell in a excel table. (ITS FORMATTED AS A TABLE)

My exceltable have values in COL A from 1 to 1005, COL B from 1 to 414 and in COL C from 414 to 1005.

What i want is to have one lastrow code that returns 414 and one that returns 1005. The problem i get is that because it is in a table. my codes

        lastrow3 = ThisWorkbook.Worksheets("DataÖnskemål").Range("A" & Rows.Count).End(xlUp).Row
        lastrow2 = ThisWorkbook.Worksheets("DataÖnskemål").Range("B" & Rows.Count).End(xlUp).Row

Both return 1005. Can i get around this with my table or is it a formating issue of some sort?

Best regards and thanks in advance /D

Upvotes: 6

Views: 73902

Answers (6)

Einarr
Einarr

Reputation: 332

I use this

'Last Row Table Column
Function LRTC(shtName As String, ColIdx As Long)
Dim ws As Worksheet
Dim x As Long, LR As Long, CN As Long

  Set ws = ThisWorkbook.Sheets(shtName)
  LR = ws.Range("A1").End(xlDown).row

  For x = LR To 1 Step -1
    If ws.Cells(x, ColIdx ).value <> "" Then
      LRTC = x
      Exit Function
    End If
  Next x

End Function

Upvotes: 0

m_zardos
m_zardos

Reputation: 35

I was searching for a similar answer, and ending up coming up with an alternative solution to what's been provided. I found it efficient in test cases. Try this case:

Dim loTable As ListObject
Dim lngRowLast1 As Long
Dim lngRowLast2 As Long

'Set reference to your specific table
Set loTable = ThisWorkbook.Sheets(1).ListObjects("Table1")
With loTable
    'I use the following as a catch in case the table is empty
    If .DataBodyRange Is Nothing Then 
        .ListRows.Add
        lngRowLast1 = 1
        lngRowLast2 = 1
    Else
        lngRowLast1 = .DataBodyRange.Cells(.ListRows.Count, 1).End(xlUp).Row
        lngRowLast2 = .DataBodyRange.Cells(.ListRows.Count, 2).End(xlUp).Row
    End If
End With

What I believe is occurring is that the End command within the worksheet will always stop at the last row of the table body, regardless if there is content in that row or not. Instead by using the End(xlUp) command from within the DataBodyRange, that then behaves as you're expecting to find the last used row.

I'm also swapping your reference from Range to Cell since I find it easier to pass in variables for the column reference.

Upvotes: 1

shakespeare
shakespeare

Reputation: 136

try this

 Dim DataRange As Range

Set DataRange = Range("A1:M" & LastRow)

Upvotes: -3

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You will have issue if there is data below the excel table on the sheet. It's always better to refer the table column while finding the last row in an excel table.

Sub FindLastRowInExcelTableColAandB()
Dim lastRow1 As Long, lastRow2 As Long
Dim ws As Worksheet
Set ws = Sheets("DataÖnskemål")
'Assuming the name of the table is "Table1"
lastRow1 = ws.ListObjects("Table1").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastRow2 = ws.ListObjects("Table1").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub

Upvotes: 15

Ambie
Ambie

Reputation: 4977

Finding last rows of tables is a bit fiddly, especially as you often need to cater for the case that a user has filtered the data. A loop with multiple checks might suit you better as you can adjust it to your own needs for the data within the table.

You also don't mention whether you can be certain that the last row is indeed a table.

In view of these points, perhaps the .Find function will suit you as it will find any non-empty cell whether in a table or not and whether hidden or not (though it doesn't cope with a filtered table). (It's not quite true to say "any non-empty cell", as a null string, for example, wouldn't be picked up, but maybe these exceptions won't trouble you). Anyhow your code could be:

With Sheet1
    lastRow1 = .Columns(1).Find(What:="*", _
                                After:=.Columns(1).Cells(1), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row

    lastRow2 = .Columns(2).Find(What:="*", _
                                After:=.Columns(2).Cells(1), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
End With

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33692

Try the code below to get the last row in Column A and Column B from a Table (ListObject):

Option Explicit

Sub LastRowTable()

Dim Tbl As ListObject
Dim LastRow2  As Long, LastRow3 As Long

' modify "Table1" to your table's Name
Set Tbl = ThisWorkbook.Worksheets("DataÖnskemål").ListObjects("Table1")

LastRow3 = Tbl.ListColumns(1).Range.Rows.Count '<-- last row in Column A in your Table
LastRow2 = Tbl.ListColumns(2).Range(LastRow3, 1).End(xlUp).Row '<-- last row  with data in Column B in your Table

End Sub

Upvotes: 1

Related Questions