CODEWITHSUNDEEP
excelvba
SikRikDaRula
SikRikDaRula

Reputation: 133

How to get last row from column in a specific range

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

Upvotes: 1

Views: 880

Answers (2)

Brian
Brian

Reputation: 2108

How about

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

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions