VBA: Select last cell in specific table column

[KyleSC]

I'm having an issue making excel select the last cell in a particular column of a table. Here is the part that is causing trouble:

     For Each lo In wksNew3.ListObjects
          Set rngAuditNoColumn = lo.ListColumns(6).DataBodyRange
                           
          'the next 2 lines remove any references to the original workbook
          rngAuditNoColumn.Copy
          rngAuditNoColumn.PasteSpecial (xlPasteValues)

          'select last cell in column 6 of table
'------------------------------>THE LINE BELOW IS THE PROBLEM! <-------------------------------------------
          Cells(lo.HeaderRowRange.Row + lo.ListRows.Count, 6).Activate
     Next lo

Current outcome:
The cells that are selected after the code runs is the range rngAuditNoColumn, which is NOT my goal. There is no error message. Do you know why the last cell of the table in the 6th column is not activated after executing this code? I feel like I'm missing something very simple.

Other info:
The name of the table won't always have the same name. The For loop is a workaround for that. The full code for the module is relatively long (for me, anyway). It involves copying worksheets, hiding and deleting table columns, and creating and saving new workbooks. Let me know if you want me to post the full module, but I'd rather avoid the hassle of removing any confidential information. Please let me know if there's any other information you require.

Thanks,

Kyle

 

[Fluff]

Is wksNew3 the active sheet when you run the code?

 

[My Aswer Is This]

This is not exactly what you want but may give you an example to look at.

Sub Select_Last_Row_Table()
'Modified 5/14/2022  7:43:41 AM  EDT
Application.ScreenUpdating = False
Dim ans As Long
    With Sheets("Alpha").ListObjects(1).DataBodyRange
        ans = .Rows.Count
        .Cells(ans, 6).Activate
    End With
Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

I think @My Aswer Is This's syntax will work for you although I would add a couple of things.
In your code "Cells.(" is not given any context which means it defaults to wksNew3.Cells. This means that your Column 6 is column 6 of the sheet not column 6 of the Table (List Object)

Adapting My Anwer Is This's syntax to your specific scenario and making a couple of other changes it becomes this:

          Application.CutCopyMode = False
          lo.DataBodyRange.Cells(lo.ListRows.Count, 6).Select

Turning cutcopymode off gets rid of the marching ants around your copy area.
Changing Activate to Select means only the Last Cell is selected. Otherwise Selection.Address and Activecell.Address will give different ranges and visually you can see the whole column still being selected
It seems unlikely that is what you want.

 

[Fluff]

Both codes will fail if wksNew3 is not the active sheet, which is why I asked the question.

 

[Fluff]

If activating the last cell is just to get rid of the whole column being selected you can use

     For Each lo In wksNew3.ListObjects
          Set rngAuditNoColumn = lo.ListColumns(6).DataBodyRange
                           
'          the next 2 lines remove any references to the original workbook
          rngAuditNoColumn.Value = rngAuditNoColumn.Value

          'select last cell in column 6 of table
'------------------------------>THE LINE BELOW IS THE PROBLEM! <-------------------------------------------
     Next lo

which will not select the cells.

 

[KyleSC]

I think @My Aswer Is This's syntax will work for you although I would add a couple of things.
In your code "Cells.(" is not given any context which means it defaults to wksNew3.Cells. This means that your Column 6 is column 6 of the sheet not column 6 of the Table (List Object)

Adapting My Anwer Is This's syntax to your specific scenario and making a couple of other changes it becomes this:

          Application.CutCopyMode = False
          lo.DataBodyRange.Cells(lo.ListRows.Count, 6).Select

Turning cutcopymode off gets rid of the marching ants around your copy area.
Changing Activate to Select means only the Last Cell is selected. Otherwise Selection.Address and Activecell.Address will give different ranges and visually you can see the whole column still being selected
It seems unlikely that is what you want.

This worked perfectly for what I needed. Sorry for the late response; I've been swamped lately. Thanks for the help!

 

[My Aswer Is This]

This worked perfectly for what I needed. Sorry for the late response; I've been swamped lately. Thanks for the help!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.