Vba to Spilt Named Range

[Biz]

Dear All,

Is there a way to check Named Range contains more 254 columns then Split Named range as Excel_Fcast1A and rest columns Excel_Fcast1B?

'Counts no of Columns in Named Range.
[FONT=Calibri]Range("Forecast").Columns.Count[/FONT]

Not sure how to split Named Range.

Biz

 

[Krishnakumar]

Hi

may be..

Sub kTest()
Dim MyNamedRange    As Range
Dim rngAddress      As String
Dim rngColumnCount  As Long

On Error Resume Next
Set MyNamedRange = Range("Forecast")
On Error GoTo 0

If Not MyNamedRange Is Nothing Then
    rngAddress = MyNamedRange.Address
    rngColumnCount = MyNamedRange.Columns.Count
    If rngColumnCount > 254 Then
        Range(Split(rngAddress, ":")(0)).Resize(MyNamedRange.Rows.Count, 254).Name = "Excel_Fcast1A"
        Range(Split(rngAddress, ":")(0)).Offset(, 254).Resize(MyNamedRange.Rows.Count, rngColumnCount - 254).Name = "Excel_Fcast1B"
    End If
End If
End Sub

 

[Biz]

Hi Kris,

It worked very well.

Biz