Find Last Row when in table when there are two tables stacked on the same tab

[gheyman]

To make things easier for the user, I have two tables on the same sheet. One (the bottom one) is refreshed based on a Power Query. The top one I am taking data from another table on another tab and pasting in the data. As an FYI: I am doing this under the assumption that I cannot refresh two tables on the same tab. I tried it and received error messages.

My question is how do I find the last row of the first table in column C? There is data in column C in the second table, which is below it, so this is giving me difficulties trying to figure out the last row in the first table. There should never be blanks in column B of the first table. and I have several rows separating the two tables.


I need to know the last row because in my VBA I want to delete all the rows (with the exceptions of the headers (it goes from Column B:L)

Then on a separate tab (PBoM Filtered to Part - table name PBoM_Filtered_to_Part) where I have the new source data for table 1 I need to count the number of rows, then go back to tab "3 Source Selection" and insert that number of rows into table 1 (which the header starts on row 15). Once the number of rows are inserted I want to copy the data from table: PBoM_Filtered_to_Part and paste it in to table 1.

I hope this explanation it clear. And Thanks for the help -your time and help is very much appreciated.

 

[gheyman]

On my tab, this table starts in row 15. In column B there is no data in column B above row 15. ( I even selected B1:B14 and did a clear all).

But when I run this code, it is selecting B16 through B13. The last row in the table named PartTask_Table is currently row 27. Can someone explain why this code is not selecting B16:B27 and is selecting B13:B16 instead? I have been working this for almost an entire day and cannot figure out how to find the last row.

Sub ClearPartTaskTable()

Dim PartTask As ListObject
Set PartTask = Sheet9.ListObjects("PartTask_Table")
 
    
 Dim lrow As Long
lrow = PartTask.Range.Rows.Count

Range("B18:B" & lrow).Select
    
    
End Sub

Ultimately, I am trying to delete rows 16 down to last row in the table.

 

[Alex Blakenburg]

Try changing your lrow line to this:

lrow = PartTask.Range.Rows(PartTask.Range.Rows.Count).Row

 

[Alex Blakenburg]

If you want the last "used" row in the table you could try this.
Where column B is the first column in the table and does not contain formulas that autofill down.

    lrow = Sheet9.Range("PartTask_Table").Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row