Get the first and last item from a range

Question

I defined a range in VBA using the following code

    Range("D3").Select
    Set x = Range(Selection, Selection.End(xlDown))

For I would like to do now is perform a function for all elements in the range except the first and last item.

So the code looks like this:

    Range("D3").Select
    Set x = Range(Selection, Selection.End(xlDown))

 For Each cell In x

       'if not first or last item

       'Do something        
    Next cell

However I can seem to get the first and last item. If I do something like x[1] of x(1) I get an error. Any thoughts on where I go wrong?

I recommend reading through [how to avoid select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Kyle, Jul 15 '16 at 15:34
@Kyle , valid point +1. I update my answer to get rid of `Select` — cyboashu, Jul 15 '16 at 15:36
@Kyle I don't use select anymore, but, when I was starting to VBA that helped me to understand what was happening; depending the case, I fix it using the same programmer logic, s/he will eventually notice as a learning curve, pointing him/her in the right direction is a nice way to introduce it :) — Sgdva, Jul 15 '16 at 15:38
We all start out using it, because it is closer to how we use Excel. I wish I would have learned earlier how to avoid it. It could have saved many headaches and many hours of debugging code. — Kyle, Jul 15 '16 at 15:42

score 1 · Answer 1 · answered Jul 15 '16 at 16:06

1

try this

Set x = ... ''<--- your initial range setting, whatever it may be 
For Each cell In x.offset(1).Resize(x.Rows.Count - 2)
       '... your code
Next cell

answered Jul 15 '16 at 16:06

user3598756

28,893
4
18
28

score 0 · Accepted Answer · answered Jul 15 '16 at 15:32

If you need to have a control in counter logic, why to use a for each instead a for/to logic?

  Range("D3").Select
    Set x = Range(Selection, Selection.End(xlDown))
TotalRows= Selection.Rows.Count
 For Counter=1 to TotalRows
 If CounterRow <> 1 and CounterRow <> TotalRows Then
       'if not first or last item

       'Do something        
 End If
    Next Counter

score 0 · Answer 3 · answered Jul 15 '16 at 15:56

Another approach, if you are performing a change to the cell values, is to load the range values into an array.

Then make the change and write the results back.

Sub ChangeValues()

Dim x() As Variant
Dim myItem As Long

Range("D3").Select

x = Range(Range("D3"), Selection.End(xlDown))

    For myItem = LBound(x, 1) + 1 To UBound(x, 1) - 1
       'if not first or last item
       'Do something
       ActiveCell.Offset(myItem - 1, 0) = x(myItem, 1) * 10
    Next myItem

End Sub

score 0 · Answer 4 · answered Apr 16 '23 at 06:02

0

You can do it in this way

From your code

Range("D3").Select
Set x = Range(Selection, Selection.End(xlDown))

and

For i=2 to x.count-1
' here your code do something with x(i)
next i

this will leave out the first and last cell of the range selected.

answered Apr 16 '23 at 06:02

Black cat

1,056
1
2
11

Get the first and last item from a range

4 Answers4