Macro command go to last filled cell in column?

[Macro command go to last filled cell in column?]

I created a macro on the weekend that does a sort and then
is supposed to go down to the last entry in a column.
What I found this morning here at work when testing the
macro on a new spreadsheet, is that although I used the
^+down arrow which when one does it manually goes to the
last filled cell, that the macro doesn't behave the same
way. Now I might have made a mistake when recoring the
macro, though I don't believe so, but at any rate, here is
the macro as it stands now:
**********************************************
Sub AddNUMBER()
'
' AddNUMBER Macro
'

'
Application.Goto Reference:="R1C6"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Application.Goto Reference:="R2C2"
Selection.End(xlDown).Select
Range("B85").Select
End Sub
**********************************************

How can I modify the above macro to go to the right spot,
the last filled entry in a column? I have all the sheets
in a workbook using the same macro so they're all going to
the same cell even though each sheet has different amounts
of data in it, which isn't what it should do. It should
go to the last entry in the column, just like what happens
when we press ^+down arrow.

Thank you.

 

[Fherrera]

I'm not 100% sure but:

if you comment out the:

Range("B85").Select

line which simpy ALWAYS selects cell B85 that's a good start.

As I believe the .End(xlDown) does what you want but the B85 rang
statement always selects B85.

Give it a shot...HT

 

[Don Guillett]

from the bottom up which is preferable
x=cells(rows.count,"B").end(xlup).row
for the top down
x=cells(activecell.row,"b").end(xldown).row
so
range("b" & x).select
But, in most cases you do NEED to goto the reference or select anything.

range("a2:x200").sort key etc

 

[PatsyB.]

-----Original Message-----
Remove the last line:

Range("B85").Select


.

OOPS!! Thank you! Although I tried to record the macro
carefully, I did slipup after all!! Yes, that correction
did do the trick!

I looked and looked through the coding initially and
didn't find that as I must admit I don't yet recognize VB
coding very well at all. I've learned another new Excel
thing today!

Thank you so much!

 

[StargateFan]

Remove the last line:

Range("B85").Select

Darn, I was cleaning up my file today at the office and I finally was
able to figure out how that B85 reference got into the macro as I
certainly didn't tell the macro to go to that particular cell..

In the original sheet that I was on when I created the macro I
recorded the keystrokes to go to the last filled cell in the column.
I then hit the down arrow key once to get the cursor where I needed
it: right _below_ the last filled cell in that column. I pressed the
down arrow key but the macro instead put an actual cell reference.
That's why this macro didn't work on all the other sheets as the
command isn't a generic one and it tells the cursor to go to B85
instead of just going to the cell immediately below.

Is there a "go one cell down" type of command that I can use for this
macro in the last line? This is vital as it's not good the way it
stands now.

Thanks!

 

[Gord Dibben]

Simon

Something like the Offset property?

Sub findbottom()
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Activate
End Sub

Finds last occupied cell in column 1 then activates the next cell down.

Gord Dibben Excel MVP

 

[Simon Shaw]

Optimally, I would name the data table, then name the columns within the table, using the named ranges in the macro is an easy way to control cell location selection and sorting. It also ensures that the macros continue to work as the layout of the data changes with new columns and/or rows.

 

[Simon Shaw]

A disadvantage of using xlup or xldown is the possibilty of blanks in the column. xlup or xldown stop at blanks, where as named tables and columns and/or table headers cells can ensure all data is correctly selected for sorting. If an entire row of data is missing, the autoselecting function that excel performs when sorting data will stop at the first all blank row, causing any data entered below to not be included in the sort.

I also find naming ranges handy for many other macros as well

Example

Say your data resides in cells A1:C10 with A1:C1 containing the table headers: Name, Address & Phone No

Assign the names
my_table to => range A1:C1
name_hdr => cell A1,
address_hdr => cell B1, an
phone_no_hdr => cell C

Then in your macro you can use
Range("My_Table").sort Key1:=.Range("address_hdr"),
Order1:=xlAscending, header:=xlTru

Range("phone_no_hdr").cells(1,0).selec
or for the last cell in the next colum
Range("phone_no_hdr").cells(Range("My_Table").Rows.Count +
Range("phone_no_hdr").Row, 0).Selec

you can also name the data columns say Name to => A2:A1
Allowing you to search for blanks, count the number of rows, select the first or last in the column, etc

 

[StargateFan]

A disadvantage of using xlup or xldown is the possibilty of blanks in the column. xlup or xldown stop at blanks, where as named tables and columns and/or table headers cells can ensure all data is correctly selected for sorting. If an entire row of data is missing, the autoselecting function that excel performs when sorting data will stop at the first all blank row, causing any data entered below to not be included in the sort.

I also find naming ranges handy for many other macros as well.

Example:

Say your data resides in cells A1:C10 with A1:C1 containing the table headers: Name, Address & Phone No.

Assign the names:
my_table to => range A1:C10
name_hdr => cell A1,
address_hdr => cell B1, and
phone_no_hdr => cell C1

Then in your macro you can use:
Range("My_Table").sort Key1:=.Range("address_hdr"), _
Order1:=xlAscending, header:=xlTrue

Range("phone_no_hdr").cells(1,0).select
or for the last cell in the next column
Range("phone_no_hdr").cells(Range("My_Table").Rows.Count + _
Range("phone_no_hdr").Row, 0).Select

you can also name the data columns say Name to => A2:A10
Allowing you to search for blanks, count the number of rows, select the first or last in the column, etc,

I got this from another Excel ng and is what I put in the last line of
the macro:

Activecell.Offset(1,0).Select

The above has worked in all the spreadsheets I added this to!

****************
This is a special instance macro. I've been forced many times in the
past to make do with a spreadsheet when a database would have been
much more appropriate. But when you're given direct orders, what do
you do??

After all these years, I decided to see if I could incorporate some db
functionality into Excel (I'm a hobbyist developer in Filemaker Pro).
That's what started these threads <g>!

And it's worked absolutely beautifully! The ability to save macros to
a workbook only and to make buttons and assign those macros to the
buttons _within_ a working (and allowing them not to print and setting
them to not move with cells, etc) has absolutely and completely
revolutionized how I can use Excel!

I have revamped practically every single spreadsheet that I have doen
in this current contract with these buttons/macros. Specifically,
index sheets for various correspondence binders. 2 in particular have
changing data. i.e., 2 of the binders are temporary binders where
legal "requests" (queries made to our legal dept) go in. Once a
request has been filled, the documentation in the requests binder gets
pulled from it and gets attached to its corresponding legal
interpretation and then gets files in the legal "interpretations"
binder. The interpretations binder is linear so the Excel spreadsheet
to use for tracking these interpretations doesn't change. The items
get entered by data so a simple Excel spreadsheet is all that is
needed. But can you imagine the headaches for the index sheet for the
requests binder??? I have numbered index tabs from 1 to 50 in that
binder so when one gets pulled, a number becomes free.

I had been using the "=ROW()-1" command for the line numbering in the
initila spreadsheet for this requests binder. That numbering
corresponds to the index tabs. When in a linear situation, the
automatic coding works just fine. With records getting added/deleted,
the automated code became a liability.

I reverted back to numbering the lines manually and when data is
changed, I now just delete the rest of the line, leaving the line
number free. When a new request comes in, it does into the freed-up
number. But what about when trying to find that request! They index
is not all out of order and is not linear!

By having 2 macros, one that sorts by the data and one that sorts by
the number, this problem is eliminated! We can sort by the data for
the hard copy of the index sheet, but keep the numbering straight by
sorting by the numbered cell for when we're maintaining/updating the
numbered cells!

How this helps:

- I have an "ADD #" macro that sorts by the numbered cell and then
goes to the space below it. This allows user to add numbered lines as
the need arises yet sorts the data by the numbers so that this doesn't
get confused and out of hand. It's visual, unfortunately, so one
doesn't have the automated record numbering that is found in databases
but I'm forced to use Excel and fortunately the number of records
being dealt with is not excessive so manually doing this is still
possible!

- but when it comes down to printing the index sheet, the PRINT button
has a sort command macro that sorts the data by date and before it
brings up the print dialogue box. The print button actually sorts the
index sheet by the data, then, before printing just like a database
would!

I know that describing something is not as clear as seeing it in
action. Just let me assure you that it works BEAUTIFULLY! With the
ADD # button/macro feature, we can maintain the index sheet yet allow
easy removing/adding of requests by sorting the sheet by the data via
the PRINT button/macro. The index sheet presents the data
chronologically while the actual requests in their tabbed indexes
might not be in any discernible order.

This allows for maximum use of Excel for this type of situation.

Hope the above wasn't as clear as mud!

Thanks for everyone's help!