在ExcelVba中选择最后8行-Selectinglast8rowsinExcelVba

5  

Try the code below (there is no need to Select the sheet in order to copy it):

请尝试以下代码(无需选择工作表以进行复制):

Option Explicit

Sub Copy_LastEight_Rows()

Dim Sht2            As Worksheet
Dim LastRow         As Long
Dim Last8Rows       As Range

Set Sht2 = ThisWorkbook.Sheets("Sheet2")

LastRow = Sht2.Range("D" & Sht2.Rows.Count).End(xlUp).Row

' modify Column D to your need
Set Last8Rows = Sht2.Range("A" & LastRow - 7 & ":D" & LastRow)
Last8Rows.Copy

End Sub

4  

When you are not sure of your range, i.e you do not know what your last column is then try this :) It uses .Find to find the last row and last column.

如果你不确定你的范围,即你不知道你的最后一列是什么,那么试试这个:)它使用.Find来查找最后一行和最后一列。

Sub Sample()
    Dim ws As Worksheet
    Dim sRow As Long, lRow As Long, lCol As Long
    Dim LastCol As String
    Dim rng As Range

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Find last row
        lRow = .Cells.Find(What:="*", _
                After:=.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

        '~~> Find last column
        lCol = .Cells.Find(What:="*", _
                After:=.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column

        '~~> Get column letter of last column
        LastCol = Split(.Cells(, lCol).Address, "$")(1)
        sRow = lRow - 7

        Set rng = .Range("A" & sRow & ":" & LastCol & lRow)

        Debug.Print rng.Address
    End With
End Sub

2  

Sheets("Sheet2").Select
LastRow = Range("D" & Rows.Count).End(xlUp).Row
Set Last8Rows =  Range("A" & LastRow - 7, "D" & LastRow)
Last8Rows.Copy

1  

Using your last code, this is it:

使用您的上一个代码,就是这样:

Option Explicit

Sub Main()

    Call SelectLastRows

End Sub

Sub SelectLastRows(Optional str_start As String = "A", _
                    Optional str_end As String = "D")

    Dim l_last_row  As Long
    Dim last_8_rows As Range


    l_last_row = Range(str_start & Rows.Count).End(xlUp).Row
    Set last_8_rows = Range(str_start & l_last_row & ":" & str_end & l_last_row).Offset(-7, 0).Resize(8, 1)
    last_8_rows.Copy

End Sub