Hi, i'm new to VBA coding. The situation is that I have a file that comes in every week with the same number of column, but with different number of rows [i.e. one week the data have 50 rows, another might have 200 etc...]. I would make a table [ctrl L] that consist of the entire 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 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

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2