Is it possible to set a range as an array using the ("A1:A10") style, or 2
named ranges such as("firstRecord:lastRecord") rather than typing each cell
in ("A1","A2","A3"...etc)?

Also, since this array will change each time the sheet is used, is it
possible to count the number of values that appear in the array to use in a
For, Next loop?

I'm basically moving data if a cell contained in the array contains an 'x'

code below if any help.

Thanks

Trevor

Sub Converted()

myRows = Worksheets("Tracker").Range("first_entry:last_entr y").Rows.Count
MyValues = Array("D6", "D7", "D8", "D9", "D10")

For i = 0 To myRows - 1

If Worksheets("Tracker").Range(MyValues(i)) = "x" Then
Worksheets("Tracker").Range("B6:c6").Offset(i, 0).Copy
Range("last_test").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

Next i

End Sub

Most wouldn't use an array for contiguous cells.

dim rng as Range
Dim cell as Range
set rng = Worksheets("Tracker").Range("D6:D10")
for each cell in rng

next cell

to loop through an array

MyValues = Array("D6", "D7", "D8", "D9", "D10")

for i = lbound(myValues) to ubound(myvalues)

Next i

--
Regards,
Tom Ogilvy


"Trevor Williams" wrote:

Is it possible to set a range as an array using the ("A1:A10") style, or 2
named ranges such as("firstRecord:lastRecord") rather than typing each cell
in ("A1","A2","A3"...etc)?

Also, since this array will change each time the sheet is used, is it
possible to count the number of values that appear in the array to use in a
For, Next loop?

I'm basically moving data if a cell contained in the array contains an 'x'

code below if any help.

Thanks

Trevor

Sub Converted()

myRows = Worksheets("Tracker").Range("first_entry:last_entr y").Rows.Count
MyValues = Array("D6", "D7", "D8", "D9", "D10")

For i = 0 To myRows - 1

If Worksheets("Tracker").Range(MyValues(i)) = "x" Then
Worksheets("Tracker").Range("B6:c6").Offset(i, 0).Copy
Range("last_test").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

Next i

End Sub

Brilliant - thanks Tom, once again you've come to the rescue.

Trevor

"Tom Ogilvy" wrote:

Most wouldn't use an array for contiguous cells.

dim rng as Range
Dim cell as Range
set rng = Worksheets("Tracker").Range("D6:D10")
for each cell in rng

next cell

to loop through an array

MyValues = Array("D6", "D7", "D8", "D9", "D10")

for i = lbound(myValues) to ubound(myvalues)

Next i

--
Regards,
Tom Ogilvy


"Trevor Williams" wrote:

Is it possible to set a range as an array using the ("A1:A10") style, or 2
named ranges such as("firstRecord:lastRecord") rather than typing each cell
in ("A1","A2","A3"...etc)?

Also, since this array will change each time the sheet is used, is it
possible to count the number of values that appear in the array to use in a
For, Next loop?

I'm basically moving data if a cell contained in the array contains an 'x'

code below if any help.

Thanks

Trevor

Sub Converted()

myRows = Worksheets("Tracker").Range("first_entry:last_entr y").Rows.Count
MyValues = Array("D6", "D7", "D8", "D9", "D10")

For i = 0 To myRows - 1

If Worksheets("Tracker").Range(MyValues(i)) = "x" Then
Worksheets("Tracker").Range("B6:c6").Offset(i, 0).Copy
Range("last_test").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

Next i

End Sub