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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

Code:
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.
 
Upvote 0
Try changing your lrow line to this:
VBA Code:
lrow = PartTask.Range.Rows(PartTask.Range.Rows.Count).Row
 
Upvote 0
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.
VBA Code:
    lrow = Sheet9.Range("PartTask_Table").Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
Upvote 0
Solution

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top