excel - 剪切选定范围VBA中的指定行数
问题描述
我有问题,我在工作表中有例如 180 行,我想随机选择例如从 A2 到工作表末尾的 18 行,除了第一个,因为会有列标题,并将其粘贴到新工作表,
解决方案
以下将实现您想要的,它将在 2 和您的最后一行数据之间生成 18 个随机数,在您的情况下为第 180 行,然后将该行复制到 Sheet2 中的下一个空闲行:
Sub foo()
Dim wsOriginal As Worksheet: Set wsOriginal = ThisWorkbook.Worksheets("Sheet1")
Dim wsDestination As Worksheet: Set wsDestination = ThisWorkbook.Worksheets("Sheet2")
'declare and set the worksheets you are working with, amend as required
Dim i As Long, LastRowOrig As Long, LastRowDest As Long
LastRowOrig = wsOriginal.Cells(wsOriginal.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A on your Sheet with data
For i = 1 To 18 'loop 18 times
RandNumber = Int((LastRowOrig - 2 + 1) * Rnd + 2)
'generate a random number between 2 and 180 (Last Row)
LastRowDest = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1
'get the last row with data on Destination sheet and offset by one (i.e. next free row)
wsOriginal.Rows(RandNumber).Copy 'copy the row
wsDestination.Rows(LastRowDest).PasteSpecial xlPasteAll 'paste the row
Next i
End Sub
更新:
要反映您的评论并添加一个包含随机行的新工作簿,请使用以下代码:
Sub foo()
Dim wsOriginal As Worksheet: Set wsOriginal = ThisWorkbook.Worksheets("Sheet1")
Dim wsDestination As Worksheet
Dim i As Long, LastRowOrig As Long, LastRowDest As Long
Set NewWorkbook = Workbooks.Add 'create a new workbook
With NewWorkbook
.Title = "Random Rows" 'You can modify this value.
.SaveAs Filename:="C:\Users\doneby\Desktop\RandomGeneratedRows.xlsx"
'amend the line above to the path you and name of the file you want to create
End With
Set wsDestination = NewWorkbook.Worksheets("Sheet1") 'specify the Sheet of the new workbook
'declare and set the worksheets you are working with, amend as required
LastRowOrig = wsOriginal.Cells(wsOriginal.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A on your Sheet with data
For i = 1 To 18 'loop 18 times
RandNumber = Int((LastRowOrig - 2 + 1) * Rnd + 2)
'generate a random number between 2 and 180 (Last Row)
LastRowDest = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1
'get the last row with data on Destination sheet and offset by one (i.e. next free row)
wsOriginal.Rows(RandNumber).Copy 'copy the row
wsDestination.Rows(LastRowDest).PasteSpecial xlPasteAll 'paste the row
Next i
NewWorkbook.Close SaveChanges:=True
'close and save the new workbook
End Sub
推荐阅读
- logstash - 在logstash中将字段从一个事件复制到另一个事件
- php - 带有数据表的moodle中的Jquery延迟异常
- java - 如何为抽象类或接口的子类型实现访问者模式?
- amazon-web-services - AWS Athena 查询输出格式
- amazon-cloudformation - 更新依赖堆栈
- python - 如何四舍五入到小数点后4位?
- c - 外部 RAM 工作和内存管理
- c++ -
将 shared_ptr作为 shared_ptr传递 - docker - Eclipse 中的 Docker 映像构建问题
- javascript - Chrome 错误的控制台:拒绝应用样式,因为它的 MIME 类型('text/html')