MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

highlight a range using a named range

  Asked By: Bryant    Date: Jan 30    Category: MS Office    Views: 2491

Here's as far as I was able to get:

lastrow = ActiveSheet.UsedRange.Rows.Count

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

where lastrow = 43

This doesn't work, but I'm hoping that one of you will come up with
something better. Thanks in advance for your help!



7 Answers Found

Answer #1    Answered By: Jack Williams     Answered On: Jan 30

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.

Answer #2    Answered By: Victoria Hughes     Answered On: Jan 30

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

Keep in mind that the Cells format is:
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:


Answer #3    Answered By: Ramond Fischer     Answered On: Jan 30

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).

Answer #4    Answered By: Cedric Sanders     Answered On: Jan 30

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.
Selection.Subtotal GroupBy:=5, Function:=xlSum,_
TotalList:=Array(9, 15, 18) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True

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"!

Answer #5    Answered By: Edjo Chalthoum     Answered On: Jan 30

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"

Answer #6    Answered By: Beau Smith     Answered On: Jan 30

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

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?

Answer #7    Answered By: Birk Fischer     Answered On: Jan 30

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)

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

End Sub

Didn't find what you were looking for? Find more on highlight a range using a named range Or get search suggestion and latest updates.