การเลือกข้อมูลทั้งหมดในแผ่นงาน VBA อื่น

คุณมีสองทางเลือก ขั้นแรก คุณสามารถใช้ตัวแปรเพื่อจัดเก็บช่วงที่คุณต้องการดำเนินการได้ ถ้าเป็นขั้นตอนครั้งเดียว ก็ไม่จำเป็นต้องใช้ตัวแปร แต่ถ้าเป็นงานที่ต้องทำซ้ำๆ หรือเกี่ยวข้องกับการอ้างอิงหลายรายการ ให้ใช้ตัวแปร

ตัวเลือกที่สองคือการอ้างอิงถึงช่วงอื่นโดยตรงโดยไม่มีตัวแปร ขอย้ำอีกครั้ง หากเป็นการดำเนินการเพียงครั้งเดียว เช่น การแสดงกล่องข้อความ

ตัวอย่างแรก:

Set rng = Sheets("My_Sheet").Cells
msgbox(rng.address)

ตัวอย่างที่สอง:

msgbox(Sheets("My_Sheet").Cells.Address)

สิ่งหนึ่งที่ควรทราบคือ คุณสมบัติ Sheet.Cells จะส่งคืนเซลล์ทั้งหมดบนชีตที่เกี่ยวข้อง ดังนั้นมันจะเป็น $A$1:$XFD$1048576 เสมอ

หากคุณต้องการเฉพาะเซลล์ที่เก็บข้อมูลจริงๆ ให้ลองใช้คุณสมบัติ UsedRange เช่น:

Set rng = Sheets("My_Sheet").UsedRange
msgbox(rng.address)

ตามการอัปเดตและการเปลี่ยนแปลงในความคิดเห็น ฉันได้รวมโค้ดชุดเต็มเพื่อประกาศช่วง และใช้ช่วงนั้นใน Pivot Table เพื่อใช้ในแผ่นงานใหม่ โปรดทราบว่าฉันไม่ได้ทำการตรวจสอบข้อผิดพลาดทั้งหมด ดังนั้นจึงควรเพิ่มสิ่งนี้ลงในโค้ด ตัวอย่างเช่น ตรวจสอบว่ามีชื่อชีตใหม่อยู่แล้ว หรือมีชื่อตารางสรุปข้อมูลอยู่แล้วหรือไม่ และหากเป็นเช่นนั้น ให้จัดการตามนั้น

Sub CreatePivotFromDynamicRange()
    Dim wsNew As Worksheet
    Dim rngData As Range
    Dim rngDestination As Range

    'Add a new worksheet to store the pivot table
    '[NOTE] if sheet named "PivotSheet" already exists it will throw error
    Set wsNew = Worksheets.Add()
    wsNew.Name = "PivotSheet"

    'Define the range with the data needed for the pivot
    Set rngData = Sheets("My_Data").UsedRange

    'define the pivot destination
    Set rngDestination = wsNew.Range("A5")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, _
        Version:=xlPivotTableVersion14).CreatePivotTable tabledestination:=rngDestination, _
        tablename:="NewPivot", defaultVersion:=xlPivotTableVersion14


End Sub

คุณไม่สามารถเลือกเซลล์จากแผ่นงานที่คุณไม่ได้ใช้งานอยู่ แต่คุณสามารถอ้างอิงเซลล์เหล่านั้นได้ คุณต้องการทำอะไรกับเซลล์ใน "My_Sheet_Name"

หากคุณต้องการที่อยู่ช่วงจากชีตอื่นและไม่ทราบแถวสุดท้ายหรือคอลัมน์สุดท้าย คุณสามารถใช้โค้ดนี้ได้

Sub SelectLastCellInInSheet()
    Dim sh As Worksheet
    Dim Rws As Long, Col As Integer, r As Range, fRng As Range

    Set sh = Sheets("My_Sheet_Name")

    With sh
        Set r = .Range("A1")
        Rws = .Cells.Find(what:="*", after:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Col = .Cells.Find(what:="*", after:=r, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set fRng = .Range(.Cells(1, 1), .Cells(Rws, Col))
    End With

    MsgBox fRng.Address
End Sub