Vba Get last used row of a Table

[My Aswer Is This]

I use this line of code to find last used row in column "A"
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

But how do I do this to find last used row in column "A" Table1
Or last used row of Table1

 

[JackDanIce]

This works for me:

With Range("Table1")
 LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
 Debug.Print LastRow
End With

Table address: D10:H22, with a single cell containing "x" in D15

 

[My Aswer Is This]

Thanks Jack that works great.

This works for me:

With Range("Table1")
 LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
 Debug.Print LastRow
End With

Table address: D10:H22, with a single cell containing "x" in D15

 

[JackDanIce]

You're welcome

 

[Akuini]

This works for me:

With Range("Table1")
 LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
 Debug.Print LastRow
End With

Table address: D10:H22, with a single cell containing "x" in D15

Hi, JackDanIce
Actually your code didn't work as I expected.
I think using 'Find' is more accurate then using 'End(xlUp)'.
Here's an example:

Sub dssgd()
Debug.Print Range("Table1").Address
With Range("Table1")
 Lastrow = .Cells(.Rows.count, 1).End(xlUp).row
  Debug.Print "Lastrow :" & Lastrow
End With

j = Range("Table1").Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
k = Range("Table1").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Debug.Print "j :" & j
Debug.Print "k :" & k
End Sub

Book1
	A	B	C
1
2	asd	dd
3	a	1
4	b	2
5	a	4
6	b	4
7	a	3
8	b	6
9		7
10		8
11	a
12
13
14
Sheet1

debug.print result:
$A$3:$B$11
Lastrow :8
j :11
k :11

 

[Akuini]

Sorry, the table area was not clear in the previous post.
It's range is $A$3:$B$11

Book1
	A	B	C
1
2	asd	dd
3	a	1
4	b	2
5	a	4
6	b	4
7	a	3
8	b	6
9		7
10		8
11	a
12
13
Sheet1

 

[My Aswer Is This]

Yes now that I work with more it's not doing what I wanted.
And I'm not sure what this means:
"Table address: D10:H22, with a single cell containing "x" in D15"


I thought just using Range("Table1") would tell the script the range

And as the table grows the range would grow. <!-- edit note -->

This works for me:

With Range("Table1")
 LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
 Debug.Print LastRow
End With

Table address: D10:H22, with a single cell containing "x" in D15

 

[Norie]

What is it you want to do?

 

[My Aswer Is This]

I wrote this script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AA:AA")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
Dim r As Long
r = Target.Row
ans = "Yes"
Dim Lastrow As Long
Lastrow = Sheets("Preapprovals").Cells(Rows.Count, "AA").End(xlUp).Row + 1
If Target.Value = ans Then Rows(r).Copy Sheets("Preapprovals").Rows(Lastrow)
End If
End Sub

But then the user said Sheet "Preapprovalas" had a table in it.

So I want to paste the row into the Tables 1st empty row.

 

[taxcel rose]

Hi,

I assume Table1 is actually a Table. Referencing Tables is supposed to be done with Listobjects.
Take a look at this: https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Are you looking for the row number within the sheet or the Table?

For the Row within the Table you can use: ActiveSheet.ListObjects("Table1").ListRows.Count