I don't see the "named range" mentioned in your subject, but you seem
to have two problems - one with parentheses, and one with reversal of rows
and columns.



To select from say C5 to J20, you would write:

With Worksheets(1)

.Range(.Cells(3, 5), .Cells(20, 10)).Select

End With



Do you see how you have incorrect parentheses, and the rows  and columns seem
to be at least partially reversed?

If this doesn't clarify for you, maybe you could let us know exactly what
you're trying to select.

I'm not sure what the PURPOSE of your macro is, so here's some
possibilities:

Keep in mind that the Cells format is:
Cells(row,column),
so C5 is cells(5,3)

now, it looks like your script is simply to select a
set of rows, not necessarily those in the range.
Let's say I have a Range(A8:D20) named  "TestRange".
But only have Data in A8-A14.

The UsedRange.Rows.Count method actually returns the row
number of the last cell with content (row 14).
But your .Select statement can select any number of cells,
independent on the Named Range, say: from row 2 thru lastrow,
which is more than the range, and does not have to even include
the range  itself. Like:

lastrow = ActiveSheet.UsedRange.Rows.Count
With Worksheets(ActiveSheet.Name)
.Range(Cells(2, 7), Cells(lastrow, 17)).Select
End With

Of course, if you already know the column LETTERS, you could use:

lastrow = ActiveSheet.UsedRange.Rows.Count
With Worksheets(ActiveSheet.Name)
.Range("G2:Q" & lastrow).Select
End With

If your intent is to actually select the named range,
then you can simply use:

Range("TestRange").Select

How about just using something Paul suggested and saying:

Range("B2:T" & lastrow).Select



The other way, using .Cells, works too, but if you want to specify B2 thru
T<lastrow>, you've got to use:

.Range(.Cells(2, 2), .Cells(lastrow, 20)).Select



It goes .Cells(Row, Column), not .Cells(Column, Row).

The parentheses problem was my last
attempt to get away from an error message that said VBA was looking
for another ")", and I've since gone back to the way that you suggest.


Here's where I started:
I have a database that has 3 columns of dollars that I wish to
subtotal, and the following works fine.
Range("B2:T43").Select
Selection.Subtotal GroupBy:=5, Function:=xlSum,_
TotalList:=Array(9, 15, 18) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("V4").Select

My problem is that T43 is the bottom row now...but it will change as
I add additional lines to the database. I want to select the cells
starting at B2 and going down to T43, or below as new lines are added.

To do this I looked for the correct bottom row with this:

lastrow = ActiveSheet.UsedRange.Rows.Count

This is correct. It returns row 43 today, and will total 44, 45, etc.
as new lines are added to the database.
Hi David,

As you can see, I stored the 43 to lastrow, and then dropped lastrow
into the following:

With Worksheets(1)
.Range (.Cells(2, 10), .Cells(20, lastrow)).Select
End With

which makes the range  line read:

.Range(.Cells(2, 10), .Cells(20, 43)).Select

...but that doesn't work  either, the error message says it is
expecting a "=".

Hope this helps to explain my predicament. I am very grateful that
you are willing to help me. I am really "stuck"!

Unfortunately I don't fully follow all of your code, but can make
the following comment based on you worded question.

You use the Term "Database", but it is not clear if you mean that
officially or that it is just 'your data area'.
*If* you truly declare it as a Database (an Excel Database - I don't
remember how... See Excel Help, not VBA Help) the database will
automatically expand when you enter new data. I don't recall if the
following is necessary, but you may be required to have one extra row
at the bottom that is kept empty, so Excel can insert and move that
cell down. It's been a long time since I used a database as a club
treasurer, but it expanded automatically for me. I also used a form
to add the data - all, I'm pretty sure, without Macros
If, on the other hand, you simply give it a range  Name

(not using the "A1:D4" format, but an actual
Range-Name and reference it something like
this line that clears a range >

Sheet3.Range("Test_Name") = ""

you can include one extra row and insert rows  or cells, in that blank
area then the Named-Range will expand to include the new cell(s).
I do this all the time with Sums and other formulas on the sheet
without macros.

You "Name" a range *in* the sheet by manually selecting it, then
giving it a name either in the upper left name box, or in the
Menu> Insert> Name> Define. Or by doing it in a Macro (I just
figured this out myself) like this:

Range("A1:F7").Name = "Test_Name"

Hopefully by now you've caught that your use of "lastrow" in your
cells reference is transposed.
It's supposed to be cells(row,column)
you're using cells(20,lastROW) which is backward.
These two are equivalent:
Range("B2:T" & lastrow).Select
Range(Cells(2,2),Cells(lastrow,20)).Select

I know, when using column LETTERS in the range  it's column,row
but when using Cell NUMBERS, it's row,column.

Does it work  now?

I use something like this in one of my macros I've adjusted it a
little, is this what you are after?

Sub DefineRange()

lastrow = ActiveSheet.UsedRange.Rows.Count

ActiveWorkbook.Names("MAMList").Delete 'deletes the existing
name range  (optional)

Sheets("Sheet1").Select
Cells(1, 1).Select 'defines the first cell of your range
Range(Selection, Selection.Offset(lastrow - 1, 3)).Select '
selects to the last row and the specified columns
Selection.Name = "MAMList" ' applies the name to the selected
range

End Sub