VBA问题：查找lastColumn并解决首行空白致列删除失效

Let’s work through this problem step by step—you’ve got solid core ideas, so let’s turn those into working, clean VBA code that solves your exact issue.

First, a quick recap: You inserted a new first row in three sheets, only filled column A with "Employee Number", and now your ManipulateSheets macro can’t delete columns because the rest of the header row is blank. We’ll implement both of your proposed approaches below.

Approach 1: Fill All Header Cells with Temporary Names #

This method populates every cell in the new first row (from column A to the last used column) with unique temporary headers. This gives your column deletion macro something to check against, so it can correctly identify which columns to remove.

Modified insertRow Macro #

Sub insertRow()
    Dim ws As Worksheet
    Dim wkbk1 As Workbook
    Dim lastCol As Long
    Dim col As Long
    
    Set wkbk1 = Workbooks("testWorkbook.xlsm")
    
    ' Loop through your target sheets to avoid repeated code
    For Each ws In wkbk1.Sheets(Array("mySheet", "hisSheet", "herSheet"))
        ws.Activate
        ' Insert new first row
        ws.Range("A1").EntireRow.Insert
        ' Set column A's header
        ws.Range("A1").Value = "Employee Number"
        
        ' Find the last used column in the current sheet
        lastCol = ws.Cells.Find(What:="*", _
                                After:=ws.Cells(1, 1), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
        
        ' Fill remaining header cells with unique temporary names
        For col = 2 To lastCol
            ws.Cells(1, col).Value = "Temp_Col_" & col
        Next col
    Next ws
End Sub

Key Fixes & Improvements: #

• Uses a loop to process all three sheets in one go (no redundant code)
• Correctly finds the last used column for each individual sheet
• Fills columns B to lastCol with unique headers like Temp_Col_2 or Temp_Col_3
  Now your original ManipulateSheets macro will recognize these headers and delete any that aren’t in your keepCols array.

Approach 2: Modify Column Deletion Macro to Handle Blank Headers #

If you don’t want to add temporary headers, you can adjust the ManipulateSheets macro to directly delete columns where the header is either blank or not in your list of columns to keep.

Modified ManipulateSheets Macro #

Sub ManipulateSheets()
    Dim a As Long, w As Long
    Dim keepCols As Variant
    Dim wkbk1 As Workbook
    Dim headerText As String
    
    Set wkbk1 = Workbooks("testWorkbook.xlsm")
    keepCols = Array("Employee Number", "Status")
    
    wkbk1.Activate
    With wkbk1
        For w = 1 To .Worksheets.Count
            With .Worksheets(w)
                ' Optional: Skip sheets that aren't your target three to avoid accidental changes
                If Not IsError(Application.Match(.Name, Array("mySheet", "hisSheet", "herSheet"), 0)) Then
                    ' Always loop from last column to first when deleting columns (critical!)
                    For a = .Columns.Count To 1 Step -1
                        headerText = Trim(.Cells(1, a).Value)
                        ' Delete if header is blank OR not in your keep list
                        If headerText = "" Or UBound(Filter(keepCols, headerText, True, vbTextCompare)) < 0 Then
                            .Columns(a).EntireColumn.Delete
                        End If
                    Next a
                End If
            End With
        Next w
    End With
End Sub

Key Fixes & Improvements: #

• Adds a check for blank header text (using Trim to handle accidental spaces)
• Combines the blank check with the existing "not in keep list" check in one condition
• Optional: Skips non-target sheets to prevent modifying other parts of your workbook
• Uses Trim to avoid issues with headers that have leading/trailing spaces

Quick Notes on Your Original Code #

• In your findColumn macro, there’s a typo: Set ws2 = wkbk1.Sheets("ws1") should probably be Set ws1 = wkbk1.Sheets("ws1") (or match your actual sheet name)
• Your original column deletion macro already loops from last to first column—great job! This is critical because deleting a column shifts remaining columns left, so looping forward would skip some columns.

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