excelvba常用代码总结1.docxVIP

Excel VBA常用代码总结1 标准化文件发布号：（9312?EUATWW?MWUB-WUNN-INNUL?DDQTY-KII Excel VBA常用代码总结1 改变背景色 RangeCAl11). = xlNone Colorlndex 一览 无色0135■891414151617 无色 0 1 3 5 ■ 8 9 14 14 15 16 17 18 15 16 17 18 1920; 21 23 24 25 26 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 改变文字颜色 Range(,,AlH). = 1 获取单元格 Cellsfl, 2) Range(,,H7H) 获取范围 Range(Cells(2/ 3), Cells(4, 5)) Range(”al:c3“) ，用快捷记号引用单元格 Worksheets(l,Sheetl,,).[Al:B5] 选中某sheet Set NewSheet = SheetsCsheetl) 选中或激活某单元格 Range对象的的Select方法可以选择一个或多个单元格，而Activate方法可以 指定某一个单元格为活动单元格。 下面的代码首先选择A1:E1O区域，同时激活D4单元格： Range(l,al:elO).Select Set objShell = Nothing 文件夹选择框方法2 (推荐) Public Function ChooseFolder() As String Dim dlgOpen As FileDialog Set dlgOpen = (msoFileDialogFolderPicker) With dlgOpen 」nitialFileName = \ If .Show = -1 Then ChooseFolder = .Selectedltems(l) End If End With Set dlgOpen = Nothing End Function ，使用方法例： Dim path As String path = ChooseFolderf) If path 11,1 Then MsgBox open folder11 End If 文件选择框方法 Public Function ChooseOneFilefOptional TitleStr As String = Please choose a file, Optional TypesDec As String = Optional Exten As String = |,*.*) As String Dim dlgOpen As FileDialog Set dlgOpen = (msoFileDialogFilePicker) With dlgOpen .Title = TitleStr TypesDec, Exten AllowMultiSelect = False InitialFileName = If .Show = -1 Then 1 ? AllowMultiSelect = True 1 For Each vrtSelectedltem In ?Selectedltems 1 MsgBox Path name: vrtSelectedltem 1 Next vrtSelectedltem ChooseOneFile = .Selectedltems(l) End If End With Set dlgOpen = Nothing End Function 某列到关键字为止循环方法1(假设关键字是end) Set CurrentCell = RangefAl1) Do While end11 Set CurrentCell = (1, 0) Loop 某列到关键字为止循环方法2(假设关键字是空字符串) i = StartRow Do While Cells? 1) i = i + l Loop For Each...Next循环(知道确切边界) For Each c In Worksheets(,,Sheetl,,).Range(,,Al:D10).Cells If Abs Then = 0 Next For Each...Next循环(不知道确切边界)，在活动单元格周圉的区域内循环 For Each c In If Abs Then = 0 Next 某列有数据的最末行的行数的取得(中间不能有空行) Ion Row=l Do While Trim(Cells(lonRow, 2 ).Value) lonRow = lonRow + 1 Loop IonRowll = lonRowl