CODEWITHSUNDEEP
vbaexcel
Lucas Poloni Cordeiro
Lucas Poloni Cordeiro

Reputation: 36

Get data range of table without using ".Select"

I have this table: enter image description here

I want the data range of my table, without the Header and the Bottom, without using .Select.

Dim MyDataFirstCell
Dim MyDataLastCell

'Establish the Data Area
ActiveSheet.Range("B1").Select ' My Table starts on Column B

'In the example the table starts at B4,
' but the user could change for B3, B5, etc.
' So I want to assure it will find the table.
ActiveCell.Offset(1, 0).Select
Do While IsEmpty(ActiveCell)
    DoEvents
    ActiveCell.Offset(1, 0).Select
Loop

'The first cell (Header) has been found.
'I need to select the first cel of my data, so:
ActiveCell.Offset(1, 0).Select
DoEvents
MyDataFirstCell = ActiveCell.Address 'Get the first cell address of Data Area

'Now I need to select the last cell of my table:

'Get to Bottom Row of the data
Selection.End(xlDown).Select

'Get to the last Column and data cell by heading to the righthand end
Selection.End(xlToRight).Select

' Select the correct last cell
ActiveCell.Offset(-1, 0).Select

'Get the Cell address of the last cell of my data area
MyDataLastCell = ActiveCell.Address


'Now I want to select this area:
Range(MyDataFirstCell & ":" & MyDataLastCell).Select

How can I code this without using ".Select"?

Upvotes: 1

Views: 606

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Combining the two links from the columns:

Dim lastrow As Long
Dim lastcolumn As Long
Dim firstrow As Long
Dim ws As Worksheet
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your sheet


With ws
    firstrow = .Cells(1, 2).End(xlDown).Row + 1
    lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row - 1
    lastcolumn = .Cells(firstrow, .Columns.Count).End(xlToLeft).Column
    Set rng = .Range(.Cells(firstrow, 2), .Cells(lastrow, lastcolumn))
End With

MsgBox rng.Address

Upvotes: 2

Related Questions