How can i achieve the following using code
i need the Range 'Fields' to 'shrink' by the first cell being removed after
some code is processed i.e

Dim Fields As Range

Fields = Range("B9,C9,I9,J9")
to
Fields = Range("C9,I9,J9")
to
Fields = Range("I9,J9")
to
Fields= Range("J9")

Cheers for all your help so far

Add the below function

Function GetTrimRange(strRange) As String
GetTrimRange = Mid(strRange, InStr(strRange, ",") + 1)
End Function

strFields = GetTrimRange("B9,C9,I9,J9")
Fields = Range(strFields)

strFields = GetTrimRange(strFields)
Fields = Range(strFields)

If this post helps click Yes
---------------
Jacob Skaria


"sunilpatel" wrote:

How can i achieve the following using code
i need the Range 'Fields' to 'shrink' by the first cell being removed after
some code is processed i.e

Dim Fields As Range

Fields = Range("B9,C9,I9,J9")
to
Fields = Range("C9,I9,J9")
to
Fields = Range("I9,J9")
to
Fields= Range("J9")

Cheers for all your help so far

Even better...Try running this..Function modified to accept range and return
range


Sub Mac()
Dim rngMyRange As Range
Dim rngMyNewRange As Range

Set rngMyRange = Range("A1,A2,A3,A4")
Set rngMyNewRange = GetTrimRange(rngMyRange)

MsgBox rngMyNewRange.Address

End Sub

Function GetTrimRange(rngTemp As Range) As Range
strTrimRange = Mid(rngTemp.Address, InStr(rngTemp.Address, ",") + 1)
Set GetTrimRange = Range(strTrimRange)
End Function


If this post helps click Yes
---------------
Jacob Skaria


"sunilpatel" wrote:

How can i achieve the following using code
i need the Range 'Fields' to 'shrink' by the first cell being removed after
some code is processed i.e

Dim Fields As Range

Fields = Range("B9,C9,I9,J9")
to
Fields = Range("C9,I9,J9")
to
Fields = Range("I9,J9")
to
Fields= Range("J9")

Cheers for all your help so far

Put the address in a String variable and then manipulate that.

Addr = "B9,C9,I9,J9"
MsgBox Range(Addr).Address
Addr = Left(Addr, InStrRev(Addr, ",") - 1)
MsgBox Range(Addr).Address
Addr = Left(Addr, InStrRev(Addr, ",") - 1)
MsgBox Range(Addr).Address
Addr = Left(Addr, InStrRev(Addr, ",") - 1)
MsgBox Range(Addr).Address

where I have used the MsgBox statements as a stand-in for your processing
code.

--
Rick (MVP - Excel)


"sunilpatel" wrote in message
...
How can i achieve the following using code
i need the Range 'Fields' to 'shrink' by the first cell being removed
after some code is processed i.e

Dim Fields As Range

Fields = Range("B9,C9,I9,J9")
to
Fields = Range("C9,I9,J9")
to
Fields = Range("I9,J9")
to
Fields= Range("J9")

Cheers for all your help so far