Finding last items in a list
 

Hi,

I am trying to write a macro to automate some data extraction from a
text file. My text file consists of three columns of numbers. The
number of lines of data varies from file to file.

I would like to use the 'slope' function to do a regression on the
last 20 lines of data but I am getting confused about how to specify
that range in the formula since the range will be different in every
text file I have.

Yell at me if I'm not making myself clear. Hope someone can help me

thanks
Ben

Finding last items in a list
 

Hi Ben,

A clumsy but effective method I use is to select the top cell in the last
(rightmost)column of data and then do Selection.End(xlDown).Select (same as
CTRL +DownArrow) on the keyboard - assuming there are no gaps in the data,
this will select the bottom cell in the column. You can then do
Range(Selection, Selection.Offset(-20, -xxx)).Select where xxx is the number
of columns to the left you want to select.

If there may be gaps in the data, then go to a cell below the last entry in
the in the rightmost column (e.g.65535) and then do
Selection.End(xlUp).Select (Same as CTRL+UpArrow on the keyboard.

Regards,

Z


" wrote:

Hi,

I am trying to write a macro to automate some data extraction from a
text file. My text file consists of three columns of numbers. The
number of lines of data varies from file to file.

I would like to use the 'slope' function to do a regression on the
last 20 lines of data but I am getting confused about how to specify
that range in the formula since the range will be different in every
text file I have.

Yell at me if I'm not making myself clear. Hope someone can help me

thanks
Ben

Finding last items in a list
 

Teriffic. That does what I need. I used:

Range(Selection, Selection.Offset(-20, -xxx)).name = "lastcells"

to name the group of cells so I could reference it in the funciton.

thanks
Ben


On 10 Oct, 10:08, Zarch wrote:
Hi Ben,

A clumsy but effective method I use is to select the top cell in the last
(rightmost)column of data and then do Selection.End(xlDown).Select (same as
CTRL +DownArrow) on the keyboard - assuming there are no gaps in the data,
this will select the bottom cell in the column. You can then do
Range(Selection, Selection.Offset(-20, -xxx)).Select where xxx is the number
of columns to the left you want to select.

If there may be gaps in the data, then go to a cell below the last entry in
the in the rightmost column (e.g.65535) and then do
Selection.End(xlUp).Select (Same as CTRL+UpArrow on the keyboard.

Regards,

Z



" wrote:
Hi,

I am trying to write a macro to automate some data extraction from a
text file. My text file consists of three columns of numbers. The
number of lines of data varies from file to file.

I would like to use the 'slope' function to do a regression on the
last 20 lines of data but I am getting confused about how to specify
that range in the formula since the range will be different in every
text file I have.

Yell at me if I'm not making myself clear. Hope someone can help me

thanks
Ben- Hide quoted text -

- Show quoted text -