在Excel Vba中选择最后8行
在Excel Vba中选择最后8行
提问于 2016-08-01 15:20:27
我想选择x行数量的最后8行(数量每月变化)，这里已经有一个线程，但只有一列。我在几个专栏中都需要它，并尝试了两种不同的方法，但由于语法的原因，这两种方法都不起作用。
Sheets("Sheet2").Select
LastRow = Range("D" & Rows.Count).End(xlUp).Row
Set Last8Rows = Range("A" & LastRow).Offset(-7, 0).Resize(8, 1)
Set Last8aRows = Range("B" & LastRow).Offset(-7, 0).Resize(8, 1)
Set Last8bRows = Range("C" & LastRow).Offset(-7, 0).Resize(8, 1)
Set Last8cRows = Range("D" & LastRow).Offset(-7, 0).Resize(8, 1)
Set Last8dRows = Range("E" & LastRow).Offset(-7, 0).Resize(8, 1)
Set Last8eRows = Range("F" & LastRow).Offset(-7, 0).Resize(8, 1)
Set Last8fRows = Range("G" & LastRow).Offset(-7, 0).Resize(8, 1)
LastxRows = Last8Rows + Last8aRows + Last8bRows + Last8cRows + Last8dRows + Last8eRows + Last8fRows
LastxRows.Copy我的第二次尝试
Sheets("Sheet2").Select
LastRow = Range("D" & Rows.Count).End(xlUp).Row
Set Last8Rows = Range("A:D" & LastRow).Offset(-7, 0).Resize(8, 1)
Last8Rows.Copy回答 4
Stack Overflow用户
回答已采纳
发布于 2016-08-01 15:28:04
尝试下面的代码(不需要通过Select复制工作表)：
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 SubStack Overflow用户
发布于 2016-08-01 15:31:14
当你不确定你的范围时，也就是你不知道你的最后一列是什么，那么试试这个:)它使用.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 SubStack Overflow用户
发布于 2016-08-01 15:34:49
Sheets("Sheet2").Select
LastRow = Range("D" & Rows.Count).End(xlUp).Row
Set Last8Rows = Range("A" & LastRow - 7, "D" & LastRow)
Last8Rows.Copy页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38693039
复制相关文章
相似问题
腾讯云开发者
