【实例05-随机提取指定行】Excel表格VBA编程实例 代码分享
Public addrow As Long
'提取并删除
Public rndrow2 As Long
Public minnum2 As Long
Public maxnum2 As Long
Private Sub CommandButton清空结果_Click()
addrow = 0
With ThisWorkbook.Worksheets("提取结果")
.UsedRange.ClearFormats
.UsedRange.ClearContents
End With
End Sub
Private Sub CommandButton提取1_Click()
With ThisWorkbook.Worksheets("操作界面")
'判断输入参数，并赋值到变量
Dim minnum As Long
Dim maxnum As Long
If .Cells(2, "C").Value <> "" And .Cells(5, "C").Value <> "" Then
minnum = .Cells(2, "C").Value
maxnum = .Cells(5, "C").Value
Else
MsgBox "参数未输入完全"
Exit Sub
End If
'生成随机提取的行
Dim rndrow As Long
rndrow = 生成随机整数(minnum, maxnum)
If rndrow > 0 Then '大于0就提取行
addrow = addrow + 1 '提取数据放置的行位置
ThisWorkbook.Worksheets("原数据").Rows(rndrow).Copy
ThisWorkbook.Worksheets("提取结果").Rows(addrow).PasteSpecial Paste:=xlPasteAll
End If
End With
'ThisWorkbook.Worksheets("提取结果").Activate
'ThisWorkbook.Worksheets("提取结果").Cells(addrow, 1).Select
End Sub
Public Function 生成随机整数(ByVal lrnd As Long, ByVal urnd As Long) As Long
'--------------------------------随机整数
生成随机整数 = Application.WorksheetFunction.RandBetween(lrnd, urnd)
End Function
Private Sub CommandButton提取2_Click()
With ThisWorkbook.Worksheets("操作界面")
'判断输入参数，并赋值到变量
'判断是否是首次提取
If addrow = 0 Then
If .Cells(2, "C").Value <> "" And .Cells(5, "C").Value <> "" Then
minnum2 = .Cells(2, "C").Value
maxnum2 = .Cells(5, "C").Value
Else
MsgBox "参数未输入完全"
Exit Sub
End If
'生成随机提取的行
rndrow2 = 生成随机整数(minnum2, maxnum2)
End If
If rndrow2 > 0 Then '大于0就提取行
addrow = addrow + 1 '提取数据放置的行位置
ThisWorkbook.Worksheets("原数据").Rows(rndrow2).Copy
ThisWorkbook.Worksheets("提取结果").Rows(addrow).PasteSpecial Paste:=xlPasteAll
ThisWorkbook.Worksheets("原数据").Rows(rndrow2).Delete
maxnum2 = maxnum2 - 1
rndrow2 = 生成随机整数(minnum2, maxnum2)
End If
End With
'ThisWorkbook.Worksheets("提取结果").Activate
'ThisWorkbook.Worksheets("提取结果").Cells(addrow, 1).Select
End Sub
