If Exists Name Function VBA

[saschmeling]

Hi,

I have a VBA program that creates a report sheet using information from other sheets. This is done monthly. I would like to format the section total rows if they exist. Here is what is going on.

The report pulls information from one workbook, and assigns names to a total row if said total row exists. Such as CapitalExpense. Capital Expenses may not exist every month, the name is created only if the section exists. This is done for 8 separate sections. Now I would like to go back and add a thick bottom border to those rows only if those rows exist.

Here is the step process for each one.

If Name Exists- select name cell
Expand selection to include 4 columns to the right of the named cell.
Add a thick bottom border to selection.
Next Name in array.

Please help if you have any suggestions.

Thanks,
Scott

 

[Mytå]

Hi

You can use this procedure

Option Explicit


Sub BorderRangeName()
  Dim NameRange As String


  NameRange = "CapitalExpense"


    If IsRangeName(NameRange) Then
        With Range(NameRange).Resize(, 4)
            With .Borders()
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        End With
    End If
    
End Sub




Public Function IsRangeName(rangeName As String) As Boolean
    On Error Resume Next
    IsRangeName = Len(Names(rangeName).Name) <> 0
End Function

Mytå

 

[Mytå]

Re

An other suggestion

Option Explicit


Sub BorderRangeName()
    Dim NameRange As String


    NameRange = "CapitalExpense"


    If IsRangeName(NameRange) Then
        ActiveWorkbook.Names.Add _
                Name:=NameRange, RefersTo:=Range(NameRange).Resize(, 4)
        Range(NameRange).BorderAround Weight:=xlThin
    End If


End Sub




Public Function IsRangeName(rangeName As String) As Boolean
    On Error Resume Next
    IsRangeName = Len(Names(rangeName).Name) <> 0
End Function

Mytå