使用以下代碼將重復的標題從合并到一個 excel 中洗掉，但出現錯誤。
Application.Goto DestSh.Cells(1)
' AutoFit the column width in the summary sheet.
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> "Combined Sheet" Then
xWs.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Dim lstRow As Integer, ws As Worksheet
Set ws = ThisWorkbook.Sheets("Combined Sheet")
With ws
lstRow = .Cells(rows.Count, "B").End(xlUp).Row ' Or "C" or "A" depends
.Range("A1:E" & lstRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete ERROR GETTING HERE
End With
在此處輸入影像描述
uj5u.com熱心網友回復：
請在使用 SpecialCells 方法之前和使用“on error GoTo 0”之后添加“on error resume next”
uj5u.com熱心網友回復：
.SpecialCells(xlCellTypeBlanks)
此運算式為您提供 Range 中的每個空白單元格。您要洗掉的行也包括非空白單元格，因此 vba 不會洗掉它們。您可以嘗試 RemoveDuplicates 方法，例如：
.Range("A1:E" & lstRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header :=xlNo
使用該方法可能不安全，但對于您的任務來說可能沒問題。
這個子是洗掉標題的安全變體。您可以通過 Call 陳述句呼叫 sub，并且不要忘記設定您的標頭地址。
Call removeHeaders()
Sub removeHeaders()
Dim hdrRangeAdr As String
Dim l, frstRow, lstRow, offsetRow As Long
Dim counter, row1, row2 As Integer
Dim item As Variant
Dim hdrRng, tRng As Range
Dim ws As Worksheet
' setting of the first header address
hdrRangeAdr = "A1:O1"
Set ws = ThisWorkbook.Sheets("Combined Sheet")
' setting of the header range
Set hdrRng = ws.Range(hdrRangeAdr)
hdrRowsQty = hdrRng.Rows.Count
frstRow = hdrRng.Row
lstRow = hdrRng.Parent.UsedRange.Rows.Count frstRow
'checking row by row
For l = 1 To lstRow - frstRow
offsetRow = l hdrRowsQty - 1
counter = 0
' compare row/rows value with the header
For Each item In hdrRng.Cells
If item = item.Offset(offsetRow, 0) Then
counter = counter 1
End If
Next
' if they are equial then delete rows
If counter = hdrRng.Count Then
row1 = frstRow offsetRow
row2 = row1 hdrRowsQty - 1
ws.Rows(row1 & ":" & row2).Delete Shift:=xlUp
'reseting values as rows qty reduced
l = 1
lstRow = hdrRng.Parent.UsedRange.Rows.Count frstRow
End If
Next
Set ws = Nothing
Set hdrRng = Nothing
End Sub
祝你好運
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/479536.html
上一篇：如何在VBA中匹配條件的顏色線