vba .End(xlToRight) 范围内
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8850881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
.End(xlToRight) within a range
提问by heavyarms
I've just got a quick one wondering if I can somehow alter the following snippet to include .End(xlToRight)instead of defining L6(the result is the same).
我只是想知道我是否可以以某种方式更改以下代码段以包含.End(xlToRight)而不是定义L6（结果是相同的）。
Sub Test()
Dim LastCol As String
With Worksheets("Sheet1")
LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Address
.Range(Range("A5"), LastCol).Copy
.Range("B5:L5", Range("B5:L5").Offset(LastRow - FirstRow, 0)).PasteSpecial xlPasteFormulas
.Range("B6", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
.Range("B6").PasteSpecial xlPasteValues
End With
End Sub
Many thanks for any help :)
非常感谢您的帮助:)
EDIT: updated pdw TonyDallimore (see response below)
编辑：更新 pdw TonyDallimore （见下面的回复）
Tony, the above code is the sum of what I've been trying to get working. Within the withstatement, I'm looking to copy the contents of row 5, and paste them down to the nth row - which is defined by a list already present in columnA. The last line per your advice will then paste the values of all but the top row (r5) to preserve transparency for the user, while minimising file size.
The middle bit is the remaining 'work in progress', as L5 is not certain to be the farmost column.
托尼，上面的代码是我一直在努力工作的总和。在该with语句中，我希望复制第 5 行的内容，并将它们粘贴到第 n 行——这是由 A 列中已经存在的列表定义的。根据您的建议，最后一行将粘贴除顶行 (r5) 之外的所有值，以保持用户的透明度，同时最小化文件大小。中间位是剩余的“正在进行的工作”，因为 L5 不确定是最远的列。
回答by Tony Dallimore
Both questions
两个问题
.End(xxx)is the same as Ctrl+Arrowfrom the keyboard and stops at the first cell that is different from the current cell. So if you start at a cell with a value it stops at a cell without a value or vice versa.
.End(xxx)Ctrl+Arrow与键盘上的相同，并在与当前单元格不同的第一个单元格处停止。因此，如果您从具有值的单元格开始，它会在没有值的单元格处停止，反之亦然。
The trouble with starting top left and using xlDownand xlToRightis that it will stop at a blank cell in the middle of your table. Fine if you absolutely cannot have a blank cell in the middle, but XlUpor xlToLeftfrom bottom right are safer.
从左上角开始并使用xlDownandxlToRight的问题在于它会停在表格中间的空白单元格处。如果您绝对不能在中间有一个空白单元格，那很好，但是从右下角XlUp或xlToLeft从右下角更安全。
Question 1
问题 1
Is your problem that .End(xxx).Columnwill return 12 and you do not know how to turn it into the letter "L"?
您的问题.End(xxx).Column会返回 12 而您不知道如何将其变成字母“L”？
Is so, there are lots of choices. I think the easiest is to remember that
是这样，有很多选择。我认为最容易记住的是
.Cells(6,Columns.Count).End(xlToLeft).Address
.Cells(6,Columns.Count).End(xlToLeft).Address
would return "$L$6".
将返回“$L$6”。
Question 2
问题2
Does .Cells(1000, ColRange)represent the bottom right cell?
是否.Cells(1000, ColRange)代表右下角细胞？
.Cells.SpecialCells(xlCellTypeLastCell)might be an easier option.
.Cells.SpecialCells(xlCellTypeLastCell)可能是一个更简单的选择。
