VBA-为什么End(xlDown)会将我带到我的Excel的最底层

本文介绍了VBA-为什么End(xlDown)会将我带到我的Excel的最底层的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

作业要求我将Monte Carlo结果运行1000次.我已经创建了一个包含30年值的行(B5:AE5)，并且我想重复此过程1000次.每次都会有新的一行出现，并且所有值都是随机的.

The assignment requires me to run the Monte Carlo result 1000 times. I already create a row of 30 years values(B5:AE5), and I want to repeat the process 1000 times. Every time, there will be a new row comes out, and all the values will be random.

下面是我的代码，由于某种原因，它将进入我的Excel工作表的最底部.我想在里面输入30年值的第二行(B6:AE6).

Below is my code, for some reason, it will go to the very bottom of my excel sheet. I want the second row of 30 years values inside (B6:AE6).

Sub Macros()
Dim trail As Long
trail = InputBox("Enter the number of time you want to simulate this Macros", "Macros", "10")

For i = 1 To trail
Application.CutCopyMode = False
Range("B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlDown).Select
Selection.Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
Range("A4").Select
Selection.End(xlDown).Select
Selection.Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMod = False
Next i
Range("B4").Select
End Sub

非常感谢！

推荐答案

要回答有关为什么 End(xlDown)将您带到工作表末尾的问题，即 Selection.End(xlDown).Select 类似于在电子表格上按 Ctrl + Down .(类似于 Selection.End(xlToRight)).Select 与按Ctrl + Right相似.)

To answer your question about why your End(xlDown) takes you to the end of the sheet, the Selection.End(xlDown).Select is similar to pressing Ctrl+Down on the spreadsheet. (Likewise Selection.End(xlToRight)).Select is similar to pressing Ctrl+Right.)

因此，如果您在空白表上，或者如果活动(或引用)单元格下面的所有单元格都为空，则按 Ctrl + Down 会将您带到最后一行.

Hence if you are on an empty sheet, or if all the cells beneath the active (or referenced) cell are empty, then pressing Ctrl+Down will bring you to the last row.

话虽如此，您可以通过

删除所有 Select 语句，然后直接使用范围对象.
使用定义的范围(B5:AE5)，因为您知道它是什么.
只需使用计数器调整范围大小即可粘贴值和格式(并消除循环).

Removing all the Select statements and work directly with the range objects.
Using the defined range (B5:AE5) since you know what it is.
Just using the counter to resize the range to to paste the values and formats (and eliminate the loop).

请参见下面的代码:

Sub Macros()

Dim trail As Long
trail = InputBox("Enter the number of time you want to simulate this Macros", "Macros", "10")

With Range(Range("B5"), Range("AE5"))
     .Copy
     .Offset(1).Resize(trail - 1, 30).PasteSpecial xlPasteValues
     .Offset(1).Resize(trai1 - 1, 30).PasteSpecial xlPasteFormats
End With

With Range("A5")
     .Copy .Offset(1).Resize(trail - 1)
End With

'if you don't need to copy the formats you can change the above With statements to just this:

'With Range("A5:BE5")
'     .Offset(i).Resize(trail - 1,31).Value = .Value
'End With

End Sub

这篇关于VBA-为什么End(xlDown)会将我带到我的Excel的最底层的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..