Reputation: 36
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
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