1

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"?

Community
  • 1
  • 1
Lucas Poloni Cordeiro
  • 36
  • 8
  • 6
    There is a very good almost canon post that is referenced all the time: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Scott Craner Sep 30 '16 at 16:56
  • 2
    You might also want to search for the best way to find the [Last Used Cell](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba "Canon Answer"), too. – Skip Intro Sep 30 '16 at 16:59
  • To find the last cell: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – Scott Craner Sep 30 '16 at 17:03
  • @ScottCraner I've seen that, but to be honest I don't think it truly answers my question. It doesn't help when a table can change position and dimension. So I need to understand how to apply that for a non fixed position dependent – Lucas Poloni Cordeiro Sep 30 '16 at 17:23

1 Answers1

2

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
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I had to add Set in "ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your sheet" to make it work. It was definetely useful, but it selected my header with it. Could you detail and try to explain how it works? thank you!!! – Lucas Poloni Cordeiro Sep 30 '16 at 17:48
  • `End` works like hitting ctrl and then the direction stipulated. I am going to the bottom and coming up to the first occupied cell or the the far right and coming back to the first occupied cell. I have edited the code to not include the header row. If it works for you please click on the check mark by the answer. – Scott Craner Sep 30 '16 at 17:55
  • Now it doesn't get the header but the range for the table is also off. It was already previously, I tried to edit my comment but I couldn't. It is showing A5:B9, when it actually should be A5:F9. – Lucas Poloni Cordeiro Sep 30 '16 at 19:03
  • then a picture of your data will help so I can test. without data it is hard to test – Scott Craner Sep 30 '16 at 19:05
  • @LucasPoloniCordeiro never mind I think I found the error. see the edit. You may need to refresh your screen – Scott Craner Sep 30 '16 at 19:06