VBA help with selecting multiple data ranges

[Kc001]

Hi, i'm new to VBA coding. The situation is that I have a file tha
comes in every week with the same number of column, but with differen
number of rows [i.e. one week the data have 50 rows, another might hav
200 etc...]. I would make a table [ctrl L] that consist of the entir
data set and name it 'X' to do further analysis.

Lets say if the column is always from A to Z, and I always want to nam
the table 'X', how would i write a macro for that?

So far all I got from reference books is:

Sub Test()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Please provide an example on how to do this [and please explain th
reasoning behind the example if possible, I am new to VBA and have n
prior programming experience

 

[Claus Busch]

Hi,

Am Thu, 16 May 2013 07:23:22 +0100 schrieb Kc001:

Lets say if the column is always from A to Z, and I always want to name
the table 'X', how would i write a macro for that?

So far all I got from reference books is:

Sub Test()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

name your table with a dynamic range name and then select this range:
Sub Test1()
ActiveWorkbook.Names.Add Name:="myTable", RefersTo:= _
"=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))"
Range("myTable").Select
End Sub

or calculate the last row and the last column:
Sub Test2()
Dim LRow As Long
Dim LCol As Integer

LRow = Cells(Rows.Count, 1).End(xlUp).Row
LCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(1, 1), Cells(LRow, LCol)).Select
End Sub


Regards
Claus Busch