Selecting multiple rows in a named range

[hdf]

I have a single column, 40 row range called NAMES.

I know I can select the first row in the range and place the value "0"
in it using the code below. But how do I select just rows 2-40 and
not the first row?

___________

Sub SelectRows()

Range("NAMES") = Clear
With Range("NAMES")
.Rows(1).Select
.Value = 0

End With

End Sub

_____________

 

[Norman Jones]

Hi H,

Try:

'=============>>
Public Sub SelectRows()
With Range("NAMES")
.ClearContents
.Resize(.Rows.Count - 1).Offset(1).Value = 0
End With
End Sub
'<<=============

 

[Zone]

.Rows("2:40").Select

 

[Evil Bumblebee]

Hi All,

I have dates in a row, in a named range.

In that range, I want to select all the rows with dates equal to or
later than 06/30/06.

Please help.

E.B.

 

[Norman Jones]

Hi Evil.

Perhaps, consider using Excel's Autofilter.

If you are not familiar with this feature, see
Debra Dalgleish's tutorial at:

Excel -- Filters -- AutoFilter Basics
http://www.contextures.com/xlautofilter01.html

If you need to automate this process, try turning
on the macro recorder while you perform the
requisite steps manully; the resultant code may be
edited to render it more generic.

If you experience problems with such editing,
post back with specific details.

 

[Evil Bumblebee]

Hi Norman,

Thank you for your reply.

No help from Autofilter in this.

Easy to sort them by date, but then I want to SELECT all the rows with
dates equal to or greater than 06/30/06.

How to select all such rows, and only in the range (not to the very
last column, and not to the last row either), so that with selected
rows then I can do more stuff?

Regards,

Evil

 

[Norman Jones]

Hi Evil,

Although it is rarely necessary, or desirable, to
select a range, I see nothing in your request that
would not conveniently be satisfied with judicious
use of the Autofilter tool.

Perhaps, however, the full extent of your
requirements is not readily apparent.

 

[Evil Bumblebee]

Norman,

If no other help, I'll work some more on it, use Autofilter and then,
with some coding, define and select the range I need, and then be able
to further work with it.

But, much faster would be something on the line of:

Sub Test()

Dim rng As Range
Dim c As Range
With ActiveSheet
Set rng = .Range("burundi")
End With
For Each c In rng
If c.Value *greater than 06/30/06* Then
*c.Range("uk_burundi").EntireRow.Select or something*
End If
Next c

End Sub

My table is 700 rows and 50 columns, and I have 20 named ranges (20
rows on average and 14 columns in each range).

Cheers,

Evil