Question

I am writing a macro that opens a New Workbook and copies all data to a different Workbook. The data always starts on C12 but we dont always know how many rows of data there are

下述法典有错误:

Workbooks("Somesheet.xls").Activate
Sheets("Sheet1").Activate
 With Range("C12").Select
 End (xlDown)
 End With

我如何从C12中遴选所有各行?

Answer 1

dim rng as range
with Workbooks("Somesheet.xls").Sheets("Sheet1").range("C12")
    set rng = range(.cells(0,0), .end(xldown))
end with

也可以使用

set rng = Workbooks("Somesheet.xls").range("C12").CurrentRegion

Answer 2

我用“Find”来检测使用过的最后一个电池,因为“AppdRange”可能不可靠,因为过度估计实际使用范围的范围,除非在使用之前不得不在代码中重新计算。只有在C12:C40测试数据时,如果提供的答复是G34:G60)

根据你的问题样本代码,你只希望C12栏(即该代码)。它可以在实际使用的栏目中随时加以扩展,或在必要时作为整整段。

Sub GetData()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Set wb = Workbooks("SomeSheet.xlsm")
    Set ws = wb.Sheets("Sheet1")
    Set rng1 = ws.Columns("C").Find("*", ws.[c1], xlFormulas, , , xlPrevious)
    If rng1.Row > 12 Then
        Set rng2 = ws.Range(ws.[c12], rng1)
        MsgBox "Your range is " & rng2.Address(0, 0)
    Else
        MsgBox "Last row in " & ws.Name & " was only " & rng1.Row
    End If
End Sub

Answer 3

为了从C12号牢房中挑选所有数据,使用<编码>UsedRange 财产来找到使用的绝对最后一行。如果在C室有一间空白处,则其他方法在表尾之前将停止。该薄膜含有从C12到表内最后使用的电池的尺寸变量:

Dim rng As Range
Dim lRowLast As Long, lColLast As Long
With ActiveWorkbook.Worksheets("Sheet1")
    lRowLast = .UsedRange.Row + .UsedRange.Rows.Count - 1
    lColLast = .UsedRange.Column + .UsedRange.Columns.Count - 1
    Set rng = .Range(.Range("C12"), .Cells(lRowLast, lColLast))
End With

注:使用<代码>。页: 1 第1栏:

<><>Edit>: The Updated example to fix the bug that @brettdj notes.

友情链接