Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard for finding items that start with ? | Excel Discussion (Misc queries) | |||
Finding multiple items from a list | Excel Worksheet Functions | |||
Data Validation: items in one list relate to items in another | Excel Programming | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Items in disabled items list - unknown excel addins causing probs | Excel Programming |