辦公室實(shí)用VBA小程序之代碼部分(三)

辦公室實(shí)用VBA小程序之代碼（2）摘要：本部分是辦公室實(shí)用VBA小程序（一）的各項(xiàng)功能的具體代碼，現(xiàn)分享給大家。我的白土受匚目當(dāng)前用戶:北國(guó)騎士

文件名：VB醐!!試.κlm

路役：C：?U≡er≡?us???Dθ≡ktqp?VB

當(dāng)前工作簿共有工作表：1張

當(dāng)前時(shí)間：2015∕□∕Ξ211:07:53恚格年檢區(qū)?？崭駲z查 Γ隱藏行檢查1合7單元格檢查-麒藤到J?查Γ如Et檢空 r『?？卟卦诎褚猞Ｇ橹Z值恰有 開始抗仃檢查2、常用功能區(qū)代碼:Sub一鍵打印()ProgressBar1.Visible=TrueApplication.ScreenUpdating=FalseApplication.DisplayAlerts=FalseProgressBar1.Min=0ProgressBar1.Max=Sheets.CountIfMSgBoX("是否已經(jīng)全部調(diào)整好打印格式？",VbYeSNo,"警告")=vbYesThenForEachMyshtInWorksheetsMysht.PrintOutActivePrinter:="RicohAficioMP2550BPCL在Ne00:"cot=cot+1ProgressBar1.Value=cotNextApplication.ScreenUpdating=TrueElseExitSubEndIfLabel2.Caption="打印完畢,共打印"&cot&"張。"cot=0EndSub'顯示被隱藏的工作表Sub顯示隱藏表()Application.ScreenUpdating=FalseForEachMyshtInActiveWorkbook.WorksheetsIfMysht.Visible<>xlSheetVisibleThenMysht.Visible=xlSheetVisiblecot=cot+1EndIfNextIfcot>0ThenLabel2.Caption="已顯示"&cot&"張被隱藏的工作表。ElseLabel2.Caption="該Workbook中無被隱藏的工作表。"EndIfcot=0Application.ScreenUpdating=TrueEndSub'隱藏非活動(dòng)工作表Sub隱藏非活表()ForEachMyshtInActiveWorkbook.WorksheetsIfMysht.Name<>ActiveSheet.NameThenMysht.Visible=xlSheetHiddencot=cot+1EndIfNextIfcot>0ThenLabel2.Caption="已隱藏"&cot&"張非活動(dòng)工作表。"ElseLabel2.Caption="該Workbook中已無非活動(dòng)工作表。”EndIfcot=0EndSubSub取消合并單元格()ForEachrngInActiveSheet.UsedRange.CellsIfrng.MergeCells=TrueThenrng.UnMergeEndIfNextLabel2.Caption="所有合并單元格取消完畢！"EndSubSub查找清除空格()DimrngAsRangeIfMSgBox("是否在查找后進(jìn)行替換？",VbYeSNo,"提醒您:")=VbYeSThenOnErrorReSumeNextForEachrngInActiVeSheet.USedRangeIfInStr(1,rng,Chr(32))Thenrng.ReplaceWhat:=Chr(32),Replacement:=""rng.Interior.Color=VbYellowcot=cot+1EndIfNextUnloadUSerForm2Ifcot=0ThenUserForm2.Label2.Caption="定位完畢，本表中無空格！"ElseUserForm2.Label2.Caption="共有"&cot&"個(gè)單元格含有空格，已黃色顯示并替換！"EndIfElseForEachrngInActiveSheet.UsedRangeOnErrorResumeNextIfInStr(1,rng,Chr(32))Thenrng.Interior.Color=vbYellowcot=cot+1EndIfNext'UnloadUserForm2Ifcot=0ThenUserForm2.Label2.Caption="定位完畢，本表中無空格！"ElseUserForm2.Label2.Caption="定位完畢，共有"&cot&"個(gè)單元格含有空格,已用黃色標(biāo)示！"EndIfEndIfcot=0EndSubOptionExplicitPublicSub標(biāo)記選區(qū)重復(fù)值()OnErrorResumeNextDimrnAsRange,firstAsRangeDimColorIdxAsIntegerDimdSetd=CreateObject("scripting.dictionary")Selection.Interior.ColorIndex=2ColorIdx=0ForEachrnInSelectionIfrn<>""ThenIfd.exists(rn.Value)ThenSetfirst=Range(d(rn.Value))'第一次出現(xiàn)的單元格Iffirst.Interior.ColorIndex=2Then'第一次出現(xiàn)時(shí)未設(shè)置過顏色I(xiàn)ColorIdx=(ColorIdx+1)Mod56+1'顏色可選范圍：0~56IfColorIdx=2ThenColorIdx=3' first.Interior.ColorIndex=ColorIdxElseColorIdx=first.Interior.ColorIndexEndIfrn.Interior.ColorIndex=ColorIdxElsed.Addrn.Value,rn.AddressEndIfEndIfNextEndSubSub另存WB()DimWbAsWorkbookMypath=ActiveWorkbook.PathSetMysht=ActiveSheetSetWb=Workbooks.AddMysht.Copybefore:=Wb.Worksheets(1)'wb.Worksheets(1).Name=MySht.NameWb.SaveAsMypath&"\"&Mysht.Name&".xlsx"Wb.CloseLabel2.Caption="已將該Sheet單獨(dú)保存在："&MyPathEndSubSub全部另存WB()DimWbAsWorkbookMyPath=ActiveWorkbook.PathAPPlication.ScreenUPdating=FalseProgressBar1.Max=ActiveWorkbook.Worksheets.CountForEachMyshtInActiveWorkbook.WorksheetsSetWb=Workbooks.AddMysht.CoPybefore:=Wb.Worksheets(1)'wb.Worksheets(1).Name=MySht.Namecot=cot+1Wb.SaveAsMyPath&"\"&Mysht.Name&".xlsx"Wb.CloseProgressBar1.Value=cotNextAPPlication.ScreenUPdating=TrueLabel2.CaPtion="已將全部工作表單獨(dú)保存在："&MyPathcot=0EndSubSub顯示所有隱行()Dimi,iiAsDoubleFori=1ToActiveSheet.UsedRange.Rows.CountIfActiveSheet.Rows(i).Hidden=TrueThenActiveSheet.Rows(i).Hidden=Falseii=ii+1EndIfNextLabel2.Caption="顯示完畢,共"&ii&"行。"EndSubSub顯示所有隱列()Dimi,iiAsDoubleFori=1ToActiveSheet.UsedRange.Columns.CountIfActiveSheet.Columns(i).Hidden=TrueThenActiveSheet.Columns(i).Hidden=Falseii=ii+1EndIfNextLabel2.Caption="顯示完畢,共"&ii&"列。EndSubOptionExplicitSub消除選區(qū)重復(fù)值()OnErrorResumeNextDimrnAsRange,resDimtarDimdSetd=CreateObject("scripting.dictionary")ForEachrnInSelectionIfrn<>""AndNotd.exists(rn.Value)Thend.Addrn.Value,""Nextres=d.keys'Fori=0Tod.Count-1'Cells(i+1,5)=res(i)'NextSettar=APPIication.InputBox(pro