【VBA】EXCEL - VBA 遍历工作表的 5 种方法，以及注意事项

1. 遍历单列数据并赋值

2. 遍历整个工作表的数据区域并赋值

3. 遍历指定范围的数据并赋值

4. 遍历多列数据并赋值

5. 遍历所有工作表中的数据并赋值

注意事项：

1. 遍历单列数据并赋值

Sub UpdateColumnData()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称Dim lastRow As LonglastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' 获取最后一行Dim i As LongFor i = 1 To lastRowws.Cells(i, 1).Value = ws.Cells(i, 1).Value * 2 ' 例如：将 A 列的每个值乘以 2Next i
End Sub

2. 遍历整个工作表的数据区域并赋值

Sub UpdateEntireSheetData()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称Dim cell As RangeFor Each cell In ws.UsedRangeIf IsNumeric(cell.Value) Thencell.Value = cell.Value + 10 ' 例如：将数值单元格的值增加 10End IfNext cell
End Sub

3. 遍历指定范围的数据并赋值

Sub UpdateSpecificRangeData()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称Dim cell As RangeFor Each cell In ws.Range("A1:E10")If Not IsEmpty(cell.Value) Thencell.Value = UCase(cell.Value) ' 例如：将文本转换为大写End IfNext cell
End Sub

4. 遍历多列数据并赋值

Sub UpdateMultipleColumnsData()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称Dim lastRow As LonglastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' 获取最后一行Dim i As Long, j As IntegerFor i = 1 To lastRowFor j = 1 To 5 ' 假设要遍历 A 到 E 列ws.Cells(i, j).Value = ws.Cells(i, j).Value & " - 已更新" ' 例如：在每个单元格后追加文本Next jNext i
End Sub

5. 遍历所有工作表中的数据并赋值

Sub UpdateAllSheetsData()Dim ws As WorksheetFor Each ws In ThisWorkbook.WorksheetsDim cell As RangeFor Each cell In ws.UsedRangeIf IsNumeric(cell.Value) Thencell.Value = cell.Value * 2 ' 例如：将数值单元格的值乘以 2End IfNext cellNext ws
End Sub

注意事项：

性能优化：对于大数据集，建议关闭屏幕更新和自动计算以提高效率

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' ... 执行操作 ...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic