范围作为变量 VBA [英] Range as a Variable VBA

问题描述

我正在尝试将值从 sheet1 复制到 sheet2.sheet1 的行数各不相同，因此我需要将其存储在变量中.我需要类似的东西:

I am trying to copy values from sheet1 to sheet2. The number of rows of sheet1 varies so I need to store it in a variable. I need something like:

Worksheets("Sheet1").Range("A2:Ab").Select

，其中b"是一个变量，用于存储 Sheet1 中的行数.

, where "b" is a variable that stores the number of rows in Sheet1.

谢谢.

推荐答案

您实际上可以存储工作表的 UsedRange.或者直接复制到另一张纸上.例如

You can actually store the UsedRange of a worksheet. Or just copy to another sheet directly. e.g.

Set oWS1 = Worksheets("Sheet1")
Set oWS2 = Worksheets("Sheet2")
oWS2.UsedRange.Clear ' Clear used range in Sheet2
oWS1.UsedRange.Copy oWS2.Range("A1") ' Copies used range of Sheet1 to A1 of Sheet2

'Set oRng = oWS1.UsedRange ' Sets a Range reference to UsedRange of Sheet1

获取工作表的最后一行:

To get the last row of a sheet:

lLastRow = oWS1.UsedRange.SpecialCells(xlLastCell).Row

编辑(从 UsedRange 的地址获取最后一行):

EDIT (Getting Last Row from UsedRange's Address):

Dim sAddr as String, lLastRow As Long
sAddr = oWS1.UsedRange.Address
lLastRow = CLng(Mid(sAddr, InStrRev(sAddr , "$") + 1))

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