RANGE QUERY
 

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

RANGE QUERY
 

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

RANGE QUERY
 

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

RANGE QUERY
 

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