求助：基于SharePoint导出Excel的P列空白单元格删除整行

Let’s figure out why your existing macro isn’t working and get it sorted for your SharePoint-exported overtime tracking sheet. The goal is to delete any row where Column P (your overtime start time column) is blank, right?

Why Your Original Code Might Be Failing #

Your initial macro has a few potential pain points that could be blocking it from working:

• Integer vs. Long: Using Integer for LastRow can cause overflow errors—Excel 2016 supports way more rows than the 32,767 limit of the Integer data type.
• LastRow Calculation: You’re using Column A to find the last row. If Column A has blank rows before the end of your data, you’ll miss rows in Column P that need checking.
• No Error Handling: If there are no blank cells in Column P, SpecialCells(xlCellTypeBlanks) throws an unhandled error that crashes the macro.
• Unspecified Worksheet: The macro relies on the active sheet, which might not be your overtime table if you have other sheets open.

Fixed Macro Version 1 (Efficient with Error Handling) #

This version addresses all those issues while keeping things fast:

Sub DeleteBlankPRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim blankRange As Range
    
    ' Target the active worksheet (replace with Sheet1 or your sheet name if needed)
    Set ws = ActiveSheet
    
    ' Calculate last row based on Column P, not Column A
    lastRow = ws.Cells(ws.Rows.Count, "P").End(xlUp).Row
    
    ' Handle case where there are no blank cells to avoid errors
    On Error Resume Next
    Set blankRange = ws.Range("P2:P" & lastRow).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    
    ' Delete rows only if blanks are found
    If Not blankRange Is Nothing Then
        blankRange.EntireRow.Delete
        MsgBox "Rows with blank Column P entries have been deleted!"
    Else
        MsgBox "No blank cells found in Column P."
    End If
End Sub

Fixed Macro Version 2 (For "Hidden" Blank Cells) #

If some cells in Column P look blank but actually have spaces or invisible characters, use this iterative approach—it also avoids skipping rows after deletion by looping from bottom to top:

Sub DeleteBlankPRows_Alternative()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "P").End(xlUp).Row
    
    ' Loop from last row up to row 2 to prevent skipping rows post-deletion
    For i = lastRow To 2 Step -1
        ' Check if cell is truly empty or only contains whitespace
        If IsEmpty(ws.Cells(i, "P")) Or Trim(ws.Cells(i, "P").Value) = "" Then
            ws.Rows(i).Delete
        End If
    Next i
    
    MsgBox "Overtime table cleanup complete!"
End Sub

How to Set This Up in PERSONAL.xlsb #

1. Open the VBA Editor with Alt + F11.
2. Navigate to your PERSONAL.xlsb project, find your existing module, and replace the old code with one of the versions above.
3. Save PERSONAL.xlsb using Ctrl + S.
4. Back in Excel, ensure macros are enabled: Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select "Enable all macros" (adjust to your security preference if needed).
5. Open your overtime tracking sheet, go to the Developer tab, click Macros, select the macro name, and hit Run.

内容的提问来源于stack exchange，提问作者Jeremy