Last row of Current table on a sheet

SanjayGMusafir

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,503
Office Version
  1. 2021
Platform
  1. MacOS
Hi Experts
Here arrives a new problem. I copy pasted a vba from 1 sheet to another. The earlier sheet it was doing fine as there was only one table in that sheet. Now the problem is that current sheet has many tables and I want it to go to the last row of current table in the current column. Please help. Thanks a lot

Code: 
    Dim lRow As Long
    Dim lCol As Long
    lRow = Cells(rows.Count, 1).End(xlUp).Row
    lCol = ActiveCell.Column
    
    'To go to last row of the same Active column
    Cells(lRow, lCol).Select
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Is this a structured Excel table OR simply tabulated data ?
 
Upvote 0
Active cell ..

before macro run

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td=bgcolor:#4472C4]Hdr1[/td][td=bgcolor:#4472C4]Hdr2[/td][td=bgcolor:#4472C4]Hdr3[/td][td=bgcolor:#4472C4]Hdr4[/td][td=bgcolor:#4472C4]Hdr5[/td][td=bgcolor:#4472C4]Hdr6[/td][td=bgcolor:#4472C4]Hdr7[/td][td=bgcolor:#4472C4]Hdr8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td=bgcolor:#D9E1F2]
31/08/2019​
[/td][td=bgcolor:#D9E1F2]
11​
[/td][td=bgcolor:#D9E1F2]
42​
[/td][td=bgcolor:#D9E1F2]
17​
[/td][td=bgcolor:#D9E1F2]
89​
[/td][td=bgcolor:#D9E1F2]
79​
[/td][td=bgcolor:#D9E1F2]
52​
[/td][td=bgcolor:#D9E1F2]
64​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td]
22/08/2019​
[/td][td]
41​
[/td][td]
74​
[/td][td]
58​
[/td][td]
31​
[/td][td]
89​
[/td][td]
82​
[/td][td]
33​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td][/td][td=bgcolor:#D9E1F2]
28/08/2019​
[/td][td=bgcolor:#D9E1F2]
7​
[/td][td=bgcolor:#D9E1F2]
72​
[/td][td=bgcolor:#D9E1F2]
3​
[/td][td=bgcolor:#D9E1F2]
87​
[/td][td=bgcolor:#D9E1F2]
5​
[/td][td=bgcolor:#D9E1F2]
88​
[/td][td=bgcolor:#D9E1F2]
28​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td=bgcolor:#4472C4]Hdr1[/td][td=bgcolor:#4472C4]Hdr2[/td][td=bgcolor:#4472C4]Hdr3[/td][td=bgcolor:#4472C4]Hdr4[/td][td=bgcolor:#4472C4]Hdr5[/td][td=bgcolor:#4472C4]Hdr6[/td][td=bgcolor:#4472C4]Hdr7[/td][td=bgcolor:#4472C4]Hdr8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td=bgcolor:#D9E1F2]
22/08/2019​
[/td][td=bgcolor:#D9E1F2]
78​
[/td][td=bgcolor:#D9E1F2]
90​
[/td][td=bgcolor:#D9E1F2]
31​
[/td][td=bgcolor:#D9E1F2]
66​
[/td][td=bgcolor:#D9E1F2]
58​
[/td][td=bgcolor:#D9E1F2]
68​
[/td][td=bgcolor:#D9E1F2]
72​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td]
31/08/2019​
[/td][td]
11​
[/td][td]
42​
[/td][td=bgcolor:#FF0000]
17​
[/td][td]
89​
[/td][td]
79​
[/td][td]
52​
[/td][td]
64​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td=bgcolor:#D9E1F2]
22/08/2019​
[/td][td=bgcolor:#D9E1F2]
41​
[/td][td=bgcolor:#D9E1F2]
74​
[/td][td=bgcolor:#D9E1F2]
58​
[/td][td=bgcolor:#D9E1F2]
31​
[/td][td=bgcolor:#D9E1F2]
89​
[/td][td=bgcolor:#D9E1F2]
82​
[/td][td=bgcolor:#D9E1F2]
33​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td]
21/08/2019​
[/td][td]
9​
[/td][td]
24​
[/td][td]
31​
[/td][td]
26​
[/td][td]
88​
[/td][td]
70​
[/td][td]
14​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td][/td][td=bgcolor:#D9E1F2]
03/09/2019​
[/td][td=bgcolor:#D9E1F2]
57​
[/td][td=bgcolor:#D9E1F2]
76​
[/td][td=bgcolor:#D9E1F2]
56​
[/td][td=bgcolor:#D9E1F2]
80​
[/td][td=bgcolor:#D9E1F2]
10​
[/td][td=bgcolor:#D9E1F2]
32​
[/td][td=bgcolor:#D9E1F2]
93​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td][/td][td=bgcolor:#4472C4]Hdr1[/td][td=bgcolor:#4472C4]Hdr2[/td][td=bgcolor:#4472C4]Hdr3[/td][td=bgcolor:#4472C4]Hdr4[/td][td=bgcolor:#4472C4]Hdr5[/td][td=bgcolor:#4472C4]Hdr6[/td][td=bgcolor:#4472C4]Hdr7[/td][td=bgcolor:#4472C4]Hdr8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td][/td][td=bgcolor:#D9E1F2]
29/08/2019​
[/td][td=bgcolor:#D9E1F2]
58​
[/td][td=bgcolor:#D9E1F2]
52​
[/td][td=bgcolor:#D9E1F2]
17​
[/td][td=bgcolor:#D9E1F2]
13​
[/td][td=bgcolor:#D9E1F2]
10​
[/td][td=bgcolor:#D9E1F2]
45​
[/td][td=bgcolor:#D9E1F2]
56​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td][/td][td]
26/08/2019​
[/td][td]
77​
[/td][td]
99​
[/td][td]
7​
[/td][td]
7​
[/td][td]
30​
[/td][td]
8​
[/td][td]
19​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td][/td][td=bgcolor:#D9E1F2]
17/08/2019​
[/td][td=bgcolor:#D9E1F2]
95​
[/td][td=bgcolor:#D9E1F2]
30​
[/td][td=bgcolor:#D9E1F2]
13​
[/td][td=bgcolor:#D9E1F2]
29​
[/td][td=bgcolor:#D9E1F2]
3​
[/td][td=bgcolor:#D9E1F2]
96​
[/td][td=bgcolor:#D9E1F2]
90​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]

after macro run

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td=bgcolor:#4472C4]Hdr1[/td][td=bgcolor:#4472C4]Hdr2[/td][td=bgcolor:#4472C4]Hdr3[/td][td=bgcolor:#4472C4]Hdr4[/td][td=bgcolor:#4472C4]Hdr5[/td][td=bgcolor:#4472C4]Hdr6[/td][td=bgcolor:#4472C4]Hdr7[/td][td=bgcolor:#4472C4]Hdr8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td=bgcolor:#D9E1F2]
31/08/2019​
[/td][td=bgcolor:#D9E1F2]
11​
[/td][td=bgcolor:#D9E1F2]
42​
[/td][td=bgcolor:#D9E1F2]
17​
[/td][td=bgcolor:#D9E1F2]
89​
[/td][td=bgcolor:#D9E1F2]
79​
[/td][td=bgcolor:#D9E1F2]
52​
[/td][td=bgcolor:#D9E1F2]
64​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td]
22/08/2019​
[/td][td]
41​
[/td][td]
74​
[/td][td]
58​
[/td][td]
31​
[/td][td]
89​
[/td][td]
82​
[/td][td]
33​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td][/td][td=bgcolor:#D9E1F2]
28/08/2019​
[/td][td=bgcolor:#D9E1F2]
7​
[/td][td=bgcolor:#D9E1F2]
72​
[/td][td=bgcolor:#D9E1F2]
3​
[/td][td=bgcolor:#D9E1F2]
87​
[/td][td=bgcolor:#D9E1F2]
5​
[/td][td=bgcolor:#D9E1F2]
88​
[/td][td=bgcolor:#D9E1F2]
28​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td=bgcolor:#4472C4]Hdr1[/td][td=bgcolor:#4472C4]Hdr2[/td][td=bgcolor:#4472C4]Hdr3[/td][td=bgcolor:#4472C4]Hdr4[/td][td=bgcolor:#4472C4]Hdr5[/td][td=bgcolor:#4472C4]Hdr6[/td][td=bgcolor:#4472C4]Hdr7[/td][td=bgcolor:#4472C4]Hdr8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td=bgcolor:#D9E1F2]
22/08/2019​
[/td][td=bgcolor:#D9E1F2]
78​
[/td][td=bgcolor:#D9E1F2]
90​
[/td][td=bgcolor:#D9E1F2]
31​
[/td][td=bgcolor:#D9E1F2]
66​
[/td][td=bgcolor:#D9E1F2]
58​
[/td][td=bgcolor:#D9E1F2]
68​
[/td][td=bgcolor:#D9E1F2]
72​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td]
31/08/2019​
[/td][td]
11​
[/td][td]
42​
[/td][td]
17​
[/td][td]
89​
[/td][td]
79​
[/td][td]
52​
[/td][td]
64​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td=bgcolor:#D9E1F2]
22/08/2019​
[/td][td=bgcolor:#D9E1F2]
41​
[/td][td=bgcolor:#D9E1F2]
74​
[/td][td=bgcolor:#D9E1F2]
58​
[/td][td=bgcolor:#D9E1F2]
31​
[/td][td=bgcolor:#D9E1F2]
89​
[/td][td=bgcolor:#D9E1F2]
82​
[/td][td=bgcolor:#D9E1F2]
33​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td]
21/08/2019​
[/td][td]
9​
[/td][td]
24​
[/td][td]
31​
[/td][td]
26​
[/td][td]
88​
[/td][td]
70​
[/td][td]
14​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td][/td][td=bgcolor:#D9E1F2]
03/09/2019​
[/td][td=bgcolor:#D9E1F2]
57​
[/td][td=bgcolor:#D9E1F2]
76​
[/td][td=bgcolor:#FF0000]
56​
[/td][td=bgcolor:#D9E1F2]
80​
[/td][td=bgcolor:#D9E1F2]
10​
[/td][td=bgcolor:#D9E1F2]
32​
[/td][td=bgcolor:#D9E1F2]
93​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td][/td][td=bgcolor:#4472C4]Hdr1[/td][td=bgcolor:#4472C4]Hdr2[/td][td=bgcolor:#4472C4]Hdr3[/td][td=bgcolor:#4472C4]Hdr4[/td][td=bgcolor:#4472C4]Hdr5[/td][td=bgcolor:#4472C4]Hdr6[/td][td=bgcolor:#4472C4]Hdr7[/td][td=bgcolor:#4472C4]Hdr8[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td][/td][td=bgcolor:#D9E1F2]
29/08/2019​
[/td][td=bgcolor:#D9E1F2]
58​
[/td][td=bgcolor:#D9E1F2]
52​
[/td][td=bgcolor:#D9E1F2]
17​
[/td][td=bgcolor:#D9E1F2]
13​
[/td][td=bgcolor:#D9E1F2]
10​
[/td][td=bgcolor:#D9E1F2]
45​
[/td][td=bgcolor:#D9E1F2]
56​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td][/td][td]
26/08/2019​
[/td][td]
77​
[/td][td]
99​
[/td][td]
7​
[/td][td]
7​
[/td][td]
30​
[/td][td]
8​
[/td][td]
19​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td][/td][td=bgcolor:#D9E1F2]
17/08/2019​
[/td][td=bgcolor:#D9E1F2]
95​
[/td][td=bgcolor:#D9E1F2]
30​
[/td][td=bgcolor:#D9E1F2]
13​
[/td][td=bgcolor:#D9E1F2]
29​
[/td][td=bgcolor:#D9E1F2]
3​
[/td][td=bgcolor:#D9E1F2]
96​
[/td][td=bgcolor:#D9E1F2]
90​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]


Code: 
Sub Sanjay()
    On Error Resume Next
    With ActiveCell.ListObject
        Intersect(.Range, ActiveCell.EntireColumn).Offset(.DataBodyRange.Rows.Count).Resize(1).Select
    End With
End Sub


ASIDE:
Do you need to physically select the cell ?
- the cell can be used by VBA and its value amended without selecting as in example code below

Code: 
Sub Sanjay2()
    Dim cel As Range
    On Error Resume Next
    With ActiveCell.ListObject
        Set cel = Intersect(.Range, ActiveCell.EntireColumn).Offset(.DataBodyRange.Rows.Count).Resize(1)
      [COLOR=#006400][I]  'get details about the cell[/I][/COLOR]
        MsgBox "Ref: " & cel.Address(0, 0) & vbCr & "Value: " & cel.Value
       [COLOR=#006400][I] 'amend its value[/I][/COLOR]
        cel.Value = "999"
    End With
End Sub


 
Upvote 0
Could have arrived at same cell in other ways too. This is how my method works:

Active cell in my example is E11 (which is in column E)

ActiveCell.ListObject returns the correct table

TABLE.Range returns all cells in the table

we need the INTERSECTing cells of ColumnE AND all cells in Table
which is E9:E14

TABLE.DataBodyRange.Rows.Count returns 5
(= rows EXCLUDING headings)

Offset E9:E14 by 5 rows moves range down to E14:E19

Finally resize E14:E19 to a single cell = E14
 
Last edited:
Upvote 0
a simpler way to get the same result (Resize is no longer required)
- HeaderRowRange is a single row
- so the INTERSECT of that and column E returns a single cell in the table header row
- which is then OFFSET as before


Code: 
Sub Sanjay3()
    On Error Resume Next
    With ActiveCell.ListObject
        Intersect(.HeaderRowRange, ActiveCell.EntireColumn).Offset(.DataBodyRange.Rows.Count).Select
    End With
End Sub
 
Upvote 0
using INTERECT can be avoided with this method

Code: 
Sub Sanjay4()
    On Error Resume Next
    With ActiveCell.ListObject
        .Parent.Cells(.HeaderRowRange.Row, ActiveCell.Column).Offset(.DataBodyRange.Rows.Count).Select
    End With
End Sub
 
Upvote 0
Thanks a lot Yongle I tried all options you gave. All of them deliver the desired result.

To my observation, INTERSECT is bit faster than rest...

Thanks again for helping in an efficient way.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Additional cells are copied when I didn't select them
Replies
7
Views
276
smozgur
smozgur
D
  • Question Question
Random selection modification not working
Replies
2
Views
357
Damian37
D
M
  • Solved
VBA Use formula within all cells within a range (across numerous columns and rows), referencing row 5 in given column
Replies
4
Views
353
Moley84
M
SanjayGMusafir
  • Solved
VBA to Jump to the last row excluding blanks
Replies
5
Views
2K
Joe4
Joe4
O
  • Solved
VBA: Deleting Visible rows using Auto Filter; Excluding Header
Replies
4
Views
821
mumps
mumps

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

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