Excel, .End(xlToLeft).Address returns address of an empty cell

Question

At the beginning of one of my subroutines I have some code that will define the dimensions of a data table that begins in Column B.

Dim TableHeaders As Variant: TableHeaders = "Table1[#Headers]"                  'Header row for the main data table
Dim MainDataTable As String: MainDataTable = "Table1"                           'Should be the main table on the BDDS
Dim MainTable As ListObject: Set MainTable = WsDB.ListObjects(MainDataTable)    'Mimics synax to call on the main data table as a variable (to make things cleaner)
Dim WholeMainTable As Range

Dim last_row As Long: last_row = WsDB.Range("A:A").Rows.Count
Dim last_row_col As Integer: last_row_col = WorksheetFunction.Match(WsDB.Range(TableHeaders).End(xlToRight), WsDB.Range(TableHeaders), 0) + 1 '+1 as the table starts in Column B
Dim first_header As String: first_header = WsDB.Range(TableHeaders).End(xlToLeft).Address

Set WholeMainTable = WsDB.Range(first_header, Cells(WsDB.Cells(last_row, last_row_col).End(xlUp), last_row_col))

The everything is working as expected except for when "first_header" is defined. End(xlToRight) gives me the correct location, yet End(xlToLeft) returns A53 instead of B53.

This code was working fine until I removed some rows above where the data table is but this code should have been able to account for that (which to be fair the row number is what it should be...)

I've double checked that there isn't anything in A53, and I've tried recording the code, selecting the table headers and manually doing ctrl (+ shift) + left. It stops at B53 as expected but when the code is run back in the macro... A53 displays again.

Answer 1

It seems as though you are trying to set WholeMainTable to the range of the whole table. If my reading of your code's intent is correct, there is a MUCH easier way:

Dim MainTable As ListObject: Set MainTable = WsDB.ListObjects(MainDataTable)
Dim WholeMainTable As Range
Set WholeMainTable = MainTable.Range

Answer 2

End always starts searching after the reference cell or top, left cell of the reference range. So if you tell it to look to the left of B53, it will find A53. If you started at C53 instead, it should find B53.