Thanks:  0
Likes:  0

# Thread: Find last cell "=INDEX(G:G,MATCH(9.99999999999999E+307,G:G))

1. I have a spreadsheet that I would like to assign a macro to. So that when I click it it will automatically auto sort a certain column. I am new to this so go easy on me please

[ This Message was edited by: mapakunk on 2002-05-14 05:45 ]

[ This Message was edited by: mapakunk on 2002-05-14 06:00 ]

2. First, you need to name the column, under Insert/Name/Define.

Then start to record your macro.
1st step - Edit/Go To/ type in column name
2nd step - Data/Sort/whatever order you want
Stop macro.

Then add a button to the worksheet via View/Toolbars/Forms/select the button and draw on sheet. When asked to assign a macro, select the one you just recorded.

Thie above only works if the column of data is always the same size. If you want a dynamic range, the Define Range formula needs to be based on the OFFSET function.

Hope this helps.

Nobby

3. That works perfectly!!

Now, how do I rename it from "Button 27" to
"Outstanding Checks" then I can use this principle from here in all my wonderful spreadsheets. Hee hee hee hu ah ha ha haaaaaaaaaa!

4. Right-click on the button and select Edit Text.

5. Now one last thing, how can I make a formula that returns the last whole value in column G to a specific cell on the worksheet (look for last entry, then send cell x to c4?)

[ This Message was edited by: mapakunk on 2002-05-14 05:46 ]

Thank you so much

7. On 2002-05-09 08:52, mapakunk wrote:
Now one last thing, how can I make a formula that returns the last whole value in column G to a specific cell on the worksheet (look for last entry, then send cell x to c4?)

[ This Message was edited by: mapakunk on 2002-05-14 05:46 ]
If G is of numeric type:

In C4 enter:

=OFFSET(G1,MATCH(9.99999999999999E+307,G:G)-1,1,1)

If G is of alphanumeric type:

=OFFSET(G1,MATCH(REPT("z"80),G:G)-1,1,1)

The second formula will ignore the last cell having a formula-generated blank.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•