时间戳

首页 > 解决方案 > Excel VBA将命名范围设置为标题

excel - Excel VBA将命名范围设置为标题

问题描述

我想将命名范围为 N2:P3 命名为“NorthHead”作为中心标题。但是，使用我的代码，我只在标题中得到单元格 N2。可能是什么问题？

Sub SetCenterHeader()
         ActiveSheet.PageSetup.CenterHeader = Range("NorthHead")
         ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

标签： excelvba

解决方案

我猜你在所有这些单元格中都有一些文本，然后加入它们：

Option Explicit

Sub SetCenterHeader()
    Dim txt As String
    Dim myRow As Range

    With Range("NorthHead") ' reference named range
        For Each myRow In .Rows ' loop through referenced range rows
            txt = txt & Join(Application.Transpose(Application.Transpose(myRow.Value)), " ") & vbLf ' update 'txt' with current row cells values joined and separated by a blank
        Next
    End With
    ActiveSheet.PageSetup.CenterHeader = Left(txt, Len(txt) - 1) ' set CenterHeader with resulting 'txt' excluding last vblf character
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

推荐阅读