一、數(shù)據(jù)處理

1、刪除重復(fù)行

Sub DeleteDuplicates()

Range('A1:B100').RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

End Sub

2、將數(shù)字格式化為貨幣

Sub FormatCurrency()

Range('A1:A10').NumberFormat = '$#,##0.00'

End Sub

3、轉(zhuǎn)換列中的文本為大寫

Sub ConvertToUpperCase()

Range('A1:A10').Value = Application.WorksheetFunction.Upper(Range('A1:A10').Value)

End Sub

二、單元格操作

4、清除單元格內(nèi)容

Sub ClearContents()

Range('A1:B10').ClearContents

End Sub

5、設(shè)置單元格背景色

Sub SetCellColor()

Range('A1').Interior.Color = RGB(255, 0, 0) ' 紅色

End Sub

6、合并多個(gè)單元格的內(nèi)容

Sub MergeCellContents()

Dim result As String

result = Join(Application.Transpose(Application.Evaluate(''' & ActiveSheet.Name & ''!R1C1:R1C10')), ' ')

Range('A11').Value = result

End Sub

三、查找與替換

7、查找并替換文本

Sub FindAndReplace()

Cells.Replace What:='OldText', Replacement:='NewText', LookAt:=xlPart, MatchCase:=False

End Sub

8、查找特定值并高亮顯示

Sub HighlightValue()

Dim cell As Range

For Each cell In Range('A1:A10')

If cell.Value = 'Target' Then

cell.Interior.Color = RGB(255, 255, 0) ' 黃色

End If

Next cell

End Sub

四、圖表與圖形

9、創(chuàng)建柱狀圖

Sub CreateBarChart()

Dim rng As Range

Set rng = Range('A1:B10')

Charts.Add

ActiveChart.SetSourceData Source:=rng

ActiveChart.ChartType = xlColumnClustered

End Sub

10、插入圖片

Sub InsertPicture()

Dim picPath As String

picPath = 'C:\path\to\your\picture.jpg'

ActiveSheet.Pictures.Insert(picPath)

End Sub

五、工作表操作

11、添加新工作表

Sub AddNewSheet()

Sheets.Add After:=Sheets(Sheets.Count)

End Sub

12、復(fù)制工作表

Sub CopySheet()

Sheets('Sheet1').Copy After:=Sheets(Sheets.Count)

End Sub

13、重命名工作表

Sub RenameSheet()

Sheets('Sheet1').Name = 'NewSheetName'

End Sub

六、自動(dòng)化與宏

14、創(chuàng)建自動(dòng)保存工作簿的宏

Sub AutoSaveWorkbook()

ThisWorkbook.Save

Application.OnTime Now + TimeValue('00:05:00'), 'AutoSaveWorkbook' ' 每5分鐘保存一次

End Sub

15、關(guān)閉所有Excel文件

Sub CloseAllExcelFiles()

Dim wb As Workbook

For Each wb In Application.Workbooks

If wb.Name <> ThisWorkbook.Name Then

wb.Close SaveChanges:=False

End If

Next wb

End Sub

七、其他功能

16、創(chuàng)建超鏈接

Sub CreateHyperlink()

ActiveSheet.Hyperlinks.Add Anchor:=Cells(1, 1), Address:='https://www.', TextToDisplay:='Visit Example'

End Sub