黄色日本 8月28日讯 据记者Kerry Hau报谈称,于帕梅卡诺和金玟哉在单独历练。 据悉,于帕梅卡诺和金玟哉当今皆莫得伤病在身,但两东谈主皆只进行了单独的历...
参考音书网萨马兰奇体育频说念12月6日报说念阿根廷布宜诺斯艾利斯经济新闻网近日发布题为《跑完马拉松后如何回复》的著作www.av,作家为马拉松换取员圣地亚哥·加...
【MVBD-011】パイパン中出し総集編 在阅读此文之前,终止您点击一下“暖热”,既绵薄您进行征询和共享,又能给您带来不雷同的参与感,感谢您的守旧。 文|赛场行...
性爱姿势 咫尺蔡爱静在文娱圈可谓是越来越火了。 咫尺的蔡爱静跟之前的蔡爱静也不相同了,愈加分解了。 “近半年,我可能皆不...
近日@91porn_soul,一款名为《黑外传:悟空》的游戏席卷了各大应付媒体平台,成为执续束缚的热议话题。这款首部国产3A大作多次禁锢游戏平台的多项记载,销量...
骨子撮要【SOE-035】ハイパー×ギリモザ ハイパーギリモザ Ami Excel表清除齐全代码1、在Userform1里,CmdChooseFolder点击事件: Dim sourceFolder As StringPrivate Sub CmdChooseFolder_Click() Dim preFolder As String preFolder = Me.TxbFolder If Not IsFolderExists(preFolder) Then preFolder = ThisWorkbook.Path End If sourceFolder = FolderSelected If Not sourceFolder = "" Then Me.TxbFolder = sourceFolder Else sourceFolder = preFolder Me.TxbFolder = sourceFolder End IfEnd Sub 代码融会:采用待清除文献夹 2、在Userform1里,OptNotFirstRow Change事件: Private Sub OptNotFirstRow_Change() If OptNotFirstRow Then Me.TxbKeyTitles.Visible = True Else Me.TxbKeyTitles.Visible = False End IfEnd Sub 代码融会:要是点中了该选项,则清楚一个文本框,要求输入表头要津字。 3、在Userform1里,CmdConfirm呐喊按钮点击事件: Private Sub CmdConfirm_Click() Dim iRow As Integer Dim colName As String Dim strTitle As String Dim arr(), arrTem(), tbTitle(), arrKey() As String, temp() Dim ws As Worksheet, lastRow As Long, lastCol As Long Dim wb As Workbook, wbTarget As Workbook, rng As Range, wsTarget As Worksheet Dim dataRow As Integer, titleRow As Integer, itemCount As Integer Dim dic As Object, dKey As String Dim FSO As Object, folder As Object, file As Object Application.ScreenUpdating = False Application.DisplayAlerts = False Set FSO = CreateObject("Scripting.FileSystemObject") Set dic = CreateObject("Scripting.Dictionary") '检查一下指标文献夹 If sourceFolder = ThisWorkbook.Path Then MsgBox "汇总文献夹不行包含本文献:" & ThisWorkbook.Name Exit Sub ElseIf Not IsFolderExists(sourceFolder) Then MsgBox "请正确采用汇总文献夹!" Exit Sub End If '判断一下文献保存姿色 If Me.CheckBox1 Then Set wbTarget = Workbooks.Add Set wsTarget = wbTarget.ActiveSheet wsTarget.Name = "汇总" Else Set wbTarget = ThisWorkbook On Error Resume Next Set wsTarget = wbTarget.Sheets("汇总") On Error GoTo 0 If wsTarget Is Nothing Then Set wsTarget = wbTarget.Worksheets.Add wsTarget.Name = "汇总" Else wsTarget.Cells.Clear End If End If If Me.OptNotFirstRow Then If Me.TxbKeyTitles = "" Then MsgBox "请输入至少一个共同表头字段!" Exit Sub End If End If Set folder = FSO.GetFolder(sourceFolder) For Each file In folder.Files fileExtn = Mid(file.Name, InStrRev(file.Name, ".")) If fileExtn Like ".xl*" And InStr(file.Name, "~$") = 0 Then Set wb = Workbooks.Open(file.Path) For Each ws In wb.Sheets ws.Activate If Me.OptFirstRow Then titleRow = 1 dataRow = 2 Else arrKey = Split(Me.TxbKeyTitles, " ") itemCount = 0 For i = 0 To UBound(arrKey) Set rng = Nothing On Error Resume Next Set rng = ws.Cells.Find(What:=arrKey(i), LookIn:=xlValues, LookAt:=xlWhole) On Error GoTo 0 If Not rng Is Nothing Then itemCount = itemCount + 1 titleRow = rng.Row dataRow = rng.Row + 1 End If Next If itemCount - 1 < UBound(arrKey) Then GoTo NextWorkSheet End If With ws lastCol = .UsedRange.Columns.Count lastRow = .UsedRange.Rows.Count itemCount = 0 For i = 1 To lastCol If .Cells(titleRow, i) <> "" Then itemCount = itemCount + 1 End If Next If itemCount <> 0 Then k = k + 1 arr = .Range(.Cells(titleRow, 1), .Cells(lastRow, lastCol)) If k = 1 Then arrTem = Application.WorksheetFunction.Transpose(arr) For i = 1 To UBound(arr, 2) dKey = arr(1, i) If dKey <> "" Then dic(dKey) = 1 End If Next Else For i = 1 To UBound(arr, 2) dKey = arr(1, i) If dKey <> "" Then If Not dic.exists(dKey) Then dic(dKey) = 1 temp = Application.WorksheetFunction.Transpose(arrTem) r = UBound(temp) ReDim Preserve temp(1 To UBound(temp), 1 To UBound(temp, 2) + 1) temp(1, UBound(temp, 2)) = dKey arrTem = Application.WorksheetFunction.Transpose(temp) End If End If Next For i = 2 To UBound(arr) ReDim Preserve arrTem(1 To UBound(arrTem), 1 To UBound(arrTem, 2) + 1) For j = 1 To UBound(arr, 2) colName = arr(1, j) If colName <> "" Then arrTem(Pxy(arrTem, colName, 1), UBound(arrTem, 2)) = arr(i, j) End If Next Next End If End If End With Erase arrNextWorkSheet: Next wb.Close End If Next On Error Resume Next b = UBound(arrTem, 2) On Error GoTo 0 If b <> "" Then wsTarget.Cells(1, 1).Resize(UBound(arrTem, 2), UBound(arrTem)) = Application.WorksheetFunction.Transpose(arrTem) End If If Me.CheckBox1 Then wbTarget.SaveAs ThisWorkbook.Path & "\清除" & Format(Now, "YYYYMMDDhhmmss") & ".xlsx" wbTarget.Close End If Unload Me MsgBox "Done!" Application.ScreenUpdating = True Application.DisplayAlerts = TrueEnd Sub 代码融会: 小萝莉渔网袜自慰流水(1)Line2~10,界说变量。 (2)Line17~23,检查待清除文献夹,不看成空,也不看成刻下文献场所文献夹。 (3)Line26~41,证实是否勾选了“保存为新文献”,来界说指标使命簿、指标使命表。 (4)Line43~48,要是采用了“非首行表头“,则文本框不看成空。 (5)Line50~126,轮回文献夹下统统文献,将允洽条目的Excel表格清除。 (A)Line56~74,要是采用了首行表头,则变量titleRow=1,不然,把要津字分列到数组,逐一查找、计数。要是找到要津字单位格,则titleRow则为找到的单位格的行号,要是莫得一齐找到,则跳过刻下表格。(B)Line79~84,检查表头字段是否为空,要是全为空,则跳过这张表。(C)Line87~95,要是是第一张表,则把通盘表装入数组arrTem,并把表头字段装入字典Dic。(D)Line96~118,要是是第二张及以后的表,先比对字段,要是字段不存在字典中,则把该字段加入字典,同期把字段加入到数组arrTem,留神这里选择两次转置的体式。字段贬责完之后,再把数据存入数组。(6)Line127~136,检查数组arrTem,要是不为空,则写入指标使命表,要是是新建的使命簿,则保存到刻下文献夹下,以“汇总”+技巧定名。 4、在Userform1里,FolderSelected自界说函数: Private Function FolderSelected(Optional title As String = "请采用文献夹......") With Application.FileDialog(msoFileDialogFolderPicker) .title = title .InitialFileName = ThisWorkbook.Path If .Show = -1 Then FolderSelected = .SelectedItems(1) Else Exit Function End If End WithEnd Function代码融会:采用文献夹。5、在Userform1里,IsFolderExists自界说函数: Function IsFolderExists(strFolder As String) As Boolean Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.folderexists(strFolder) Then IsFolderExists = True End IfEnd Function 代码融会:判断文献夹是否存在。 6、在Userform1里,Pxy自界说函数,数组字段定位: Function Pxy(arr(), FieldName As String, Optional arrType As Integer = 0) '********************************** 'arrType=0,默示一维数组 'arrType=1,默示二维数组,查找第一列 'arrType=2,默示二维数组,查找第一转 '********************************** k = 0 t = 0 Select Case arrType Case Is = 0 For i = LBound(arr) To UBound(arr) k = k + 1 If arr(i) = FieldName Then t = 1 Exit For End If Next Case Is = 1 For i = LBound(arr, 1) To UBound(arr, 1) k = k + 1 If arr(i, 1) = FieldName Then t = 1 Exit For End If Next Case Is = 2 For i = LBound(arr, 2) To UBound(arr, 2) k = k + 1 If arr(1, i) = FieldName Then t = 1 Exit For End If Next End Select If t = 1 Then Pxy = k Else Pxy = 0 End IfEnd Function 代码融会:数组字段定位,不错定位一维数组字段位置,二维数组行首行、首列标的字段位置。 7、在Userform1里,退出按钮: Private Sub CmdExit_Click() Unload MeEnd Sub 8、在Sheet(清除)里,CmdMerge清除按钮: Private Sub CmdMerge_Click() UserForm1.ShowEnd Sub~~~~~~End~~~~~~ 本站仅提供存储就业,统统骨子均由用户发布,如发现存害或侵权骨子,请点击举报。 |