This should be easy

[sdinyer]

Hi,

I updated to Excel 2011 for MAC - Mac Os X 10.6.4

I have a column set of Name, Check Number, Date, Amount, etc (what's there is not important(it's about 8 columns by X amount of rows)) this gets progressively longer in rows as time goes by because of daily entries.

I have an easy input macro which lets the operator enter the data in a form and it goes right into the rows/columns and sets it back to the form, ready to input the next set. (That's not important either, just to say; it works and advances the rows down and all my formulas update)

To Make it easy for the operator to sort the list by date or check, I am making a macro that will sort by only those two fields,, so that the operator doesn't do something unfixable using the regular sort button. Please, let me know if I am giving too much information.

Below is the Macro I started (without the sort strings). I have other formula and sections in the worksheet besides this list of checks. So what I am trying to do is to have Excel go to the end of this section. By the end I mean 1 cell right of the last column of the last row. It then Moves over left one Cell which would put the selection right on the Last Cell of the List. This part works fine.

My goal is to Pick that cell as the end of the selection (which that Cell/Row Number will change every day) then go to (what will always be a constant) Cell "A17" (which is the start) to have that as the beginning of the selection. This will give me Range A17 through all the rows and columns ending on That Last Cell, Whatever cell it might be. Then I can set up various sort functions what ever is needed for just that selection. These will be assigned to buttons.

The Macro as it stands has various Statements below the line which says (ActiveCell.Offset(0, -1).Select)

These are all the various things I tried,,, Not all at the same time but none of the combinations worked. I found some of these statements in tutorials. it doesn't seem to want to go past selecting the last Cell. But if I use the line Range("A17:H246").Select, Which is the range of Cells currently there before any more entries, I'm afraid That being in the macro A17:H246 will not update as the formulas do,,, Although, stupid me, I haven't actually tried that. This is what happened to me in my last data base though.(not excel)

I hope I made some sense. Here is the macro

Sub GoToEndOfData()
'
' GoToEndOfData Macro
' Goes to End of Data ThenLeft arrow to Last Field

Application.Goto Reference:="EndOfData"

ActiveCell.Offset(0, -1).Select

' EndCell = ActiveCell.Address
' Range("A1:EndCell").Select


' Range("ActiveCell:A17 ").Select

' Set = Range("A17:" & EndCell)
' Set MyRange = Range("A17:" & EndCell)

' Range("A17:strCellAddress").Select

' Range("A17:H246").Select

End Sub

That last line of course works as I stated above, but I believe only until I start adding rows?

By the way I am not by the worksheet right now I'll have to try any suggestions later tonight.

Thank You,
Steve

 

[CWatts]

I think you scared people with that explanation!

Can you convert your data range to a table format? That will make sorting and filtering very easy for your users and should limit the sorting problems you're afraid of (such as rearranging two columns and not the others, invalidating all relevance of your data).

You can also reference the column heading which will dynamically include all rows in the table.

 

[sdinyer]

Thank You for your reply.

I am very unfamiliar with Excel, its Functions, and VBA. I have used other spreadsheets a long time ago. I had a way that I like to lay out things like this, which I think is the wrong way according to what I see people do in Excel tutorials, but it works great and looks great. I tend to make very involved spreadsheets with everything laid out on one page along the top with the List below. Many fields and sub categories are included right there where you can see it. I'm not saying your wrong. I'm saying it might be wrong for me, they way I started out this project.

Directly over the List of checks (Sharing the same columns) I have all kinds of formulas and calculations. And that is where I put the form for entering data, which ends up in the list of checks, below. (also sharing some of the columns). If I just reference the column, I think, it will include the stuff above it, that I don't want. That's why I'd like to use a Macro to work on just that section. Which is what I did in the past. I did name the list columns but it hasn't worked very well for referencing. I might have done something wrong, there in the way I named it. I don't know.

I will look at tables to see the advantages but I must say I love they way my layouts work. (if I can figure out the things I don't know how to do).
You're right. I do go on and on.

Thanks
Steve

 

[sdinyer]

HA! Ha, Ha, Ha!, Ha!!

I figured it out all by my self!!! It took me hours but I finally got It

The Magic String is this

Range(Cells(17, 1), ActiveCell).Select

Works Perfectly.. Thanks
and Sorry for my long, long explanation I guess it was scary
Steve