How to get last row from column in a specific range

Question

I have some code that opens an excel spreadsheet and gets the last empty row in that column. The problem I am having is that I have two tables created in my excel spreadsheet. I want to be able to only select the range of B7:B94 for column B as I have data that represents another table starting at B:101 Here is my code so far...

Const xlUp = -4162 Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = False Set xlWB = .Workbooks.Open("M:\Shared Documents\Job Cost Analysis\Hi-Tech BPO\Logs\" & currentMonth & "-Summary Hi Tech BPO.xlsx", , False) Set ws = .Worksheets(sheetName) Dim LR '''''''''''Here is where I want to select the range of B7:B94'''''''''''''' LR = .Range("B" & .Rows.count).End(xlUp).Row .Range("B" & LR + 1).Value = RIGHT(client_id,LEN(client_id)-7) End With xlApp.DisplayAlerts = False xlWB.SaveAs ("M:\Shared Documents\Job Cost Analysis\Hi-Tech BPO\Logs\" & currentMonth & "-Summary Hi Tech BPO.xlsx") xlWB.Close xlApp.Quit

Are those actual ListObject (aka structured) tables or just blocks of data? — user4039065
– user4039065, Commented May 9, 2016 at 17:42

user4039065 · Accepted Answer · 2016-05-09 17:46:24Z

For unstructured blocks of data,

LR = .Range("B100").End(xlUp).Row

For true ListObject (aka structured) tables,

LR = .Range("B100").End(xlUp).End(xlUp).Row

The latter assumes that the table is not 'full'. A check to ensure LR is not 7 should be made.

Brian · Accepted Answer · 2016-05-09 17:48:42Z

1

How about

.Range("B7").End(xlDown).Offset(1,0).Row

edited May 9, 2016 at 17:48

answered May 9, 2016 at 17:39

Brian

2,1182 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to get last row from column in a specific range

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related