VBA动态范围 [英] VBA Dynamic Ranges

问题描述

我已经将下面的代码放在一起，在我的工作簿中创建一个新的工作表，并应用动态命名范围和页面格式。

  Sub AllDataNamedRanges（）
 
 Dim rLOB As Range 
 Dim rStaffName As Range 
 Dim rTask As Range 
 Dim rProjectName As Range 
 Dim rProjectID As Range 
 Dim rJobRole As Range 
 Dim rMonth As Range 
 Dim rActuals As范围
 
设置rLOB =范围（[B4]，[B4] .End（xlDown））
设置rStaffName =范围（[C4]，[C4] .End（xlDown））
设置rTask =范围（[D4]，[D4] .End（xlDown））
设置rProjectName =范围（[E4]，[E4] .End（xlDown））
设置rProjectID =范围（[F4]，[F4] .End（xlDown））
设置rJobRole =范围（[G4]，[G4] .End（xlDown））
设置rMonth =范围（[H4] [H4] .End（xlDown））
设置rActuals =范围（[I4]，[I4] .End（xlDown））
 
表单（AllData）选择
 
 ActiveWorkbook.Names.Add名称：=LOB，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rLOB.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=StaffName，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rStaffName.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=任务，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rTask.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=ProjectName，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rProjectName.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=ProjectID，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rProjectID.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=JobRole，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rJobRole.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=Month，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rMonth.Address（ReferenceStyle：= xlR1C1）
 
 ActiveWorkbook.Names.Add名称：=Actuals，RefersToR1C1：==& _ 
 ActiveSheet.Name& ！ &安培; rActuals.Address（ReferenceStyle：= xlR1C1）
 
 End Sub 
  

代码做的工作，但我有点担心，它可能有点笨重，可以写得更聪明。我对VBA来说比较新，但我愿意学习。

我只是想知道一个人可能比我更经验丰富的程序员，可以看看这个请给我一些关于我如何能够写得更好的指导。

非常感谢和善意

解决方案

最好的方法是不要通过代码来完成，但使用动态命名范围，这将在您添加新数据时更改范围。

下面的命名范围公式设置一个动态命名范围覆盖范围 Sheet1！$ A $ 4：$ A $ 1000

  = OFFSET（Sheet1！$ A $ 4,0,0，COUNTA（Sheet1！$ A $ 4：$ A $ 1000），1）
  

1. 公式/名称管理器


2. 新


3. 输入名称，范围，并引用上述公式（注释是可选的）


4. 确定

您也可以使用整列A：A，但是如果从A4开始计数，则需要调整A1：A3中的值的单元格数。在图片示例中，将是

  = OFFSET（Sheet1！$ A $ 4,0,0，COUNTA（Sheet1！$ A ：$ A）-1,1）
  

I wonder whether someone may be able to help me please.

I've put together the code below which creates a new sheet in my workbook and applies dynamic named ranges and page formatting.

Sub AllDataNamedRanges()

Dim rLOB As Range
Dim rStaffName As Range
Dim rTask As Range
Dim rProjectName As Range
Dim rProjectID As Range
Dim rJobRole As Range
Dim rMonth As Range
Dim rActuals As Range

Set rLOB = Range([B4], [B4].End(xlDown))
Set rStaffName = Range([C4], [C4].End(xlDown))
Set rTask = Range([D4], [D4].End(xlDown))
Set rProjectName = Range([E4], [E4].End(xlDown))
Set rProjectID = Range([F4], [F4].End(xlDown))
Set rJobRole = Range([G4], [G4].End(xlDown))
Set rMonth = Range([H4], [H4].End(xlDown))
Set rActuals = Range([I4], [I4].End(xlDown))

Sheets("AllData").Select

    ActiveWorkbook.Names.Add Name:="LOB", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rLOB.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="StaffName", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rStaffName.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="Task", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rTask.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="ProjectName", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rProjectName.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="ProjectID", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rProjectID.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="JobRole", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rJobRole.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="Month", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rMonth.Address(ReferenceStyle:=xlR1C1)

    ActiveWorkbook.Names.Add Name:="Actuals", RefersToR1C1:="=" & _
    ActiveSheet.Name & "!" & rActuals.Address(ReferenceStyle:=xlR1C1)

End Sub

The code does work but I'm a little concerned that it may be a little clunky and could be written smarter. I'm relatively new to VBA but I'm willing to learn.

I just wondered whether someone, who is perhaps a more seasoned programmer than I, could look at this please and offer some guidance on how I may be able to write this a little better.

Many thanks and kind regards

解决方案

The best way is not to do it via code at all but use a dynamic named range which will change the range as you add new data.

The named range formula below sets a dynamic named range covering range Sheet1!$A$4:$A$1000

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$1000),1)

1. Formulas/Name Manager
2. New
3. Enter Name, scope, and refers to formula above (comments are optional)
4. OK

You could also use the whole column A:A but if you start counting from A4 then you need to adjust for the number of cells with value in A1:A3. In the picture example it would be

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-1,1)

这篇关于VBA动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！