VBA:按变量选择范围 [英] VBA: Selecting range by variables
问题描述
我想选择 Excel 工作表的格式化范围.要定义最后一行和第一行,我使用以下函数:
I want to select the formatted range of an Excel sheet. To define the last and first row I use the following functions:
lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
下一步我想选择这个区域:公式应如下所示:
In the next step I want to select this area: Formula should look like this:
Range(cells(1, 1), cells(lastRow, lastColumn).Select
然而,这是行不通的.也许有人知道它有什么问题.非常感谢!
However, this is not working. Maybe somebody has an idea what is wrong with it. Thanks a lot!
推荐答案
我用相对引用"录制了一个宏,这就是我得到的:
I recorded a macro with 'Relative References' and this is what I got :
Range("F10").Select
ActiveCell.Offset(0, 3).Range("A1:D11").Select
这就是我的想法:如果范围选择是在引号中,VBA 真的需要一个 STRING 并从中解释单元格,所以尝试了以下操作:
Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:
Dim MyRange as String
MyRange = "A1:D11"
Range(MyRange).Select
它奏效了 :) 即.. 只需使用您的变量创建一个字符串,确保将其作为字符串变量进行标注,Excel 将立即读取它 ;)
And it worked :) ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it ;)
以下测试并发现有效:
Sub Macro04()
Dim Copyrange As String
Startrow = 1
Lastrow = 11
Let Copyrange = "A" & Startrow & ":" & "D" & Lastrow
Range(Copyrange).Select
End Sub
这篇关于VBA:按变量选择范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!