Excel中如何实现按条件复制加粗单元格的内容？

Alright, since your dataset is way too large for manual edits, VBA is the perfect tool here. This script will automate exactly what you need: check the cell in the previous column, copy its value if it's bold, and if not, crawl upwards until it finds the first bold cell in that column to copy over.

Here's the VBA code you can use: #

Sub CopyBoldPreviousColumn()
    Dim ws As Worksheet
    Dim targetCol As Integer, sourceCol As Integer
    Dim lastRow As Long, i As Long, j As Long
    Dim boldValue As Variant
    
    ' Set your worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Define columns: targetCol is where you want to paste values, sourceCol is the column before it
    targetCol = 2 ' Example: Column B is target, Column A is source
    sourceCol = targetCol - 1
    
    ' Get the last row with data in the target column
    lastRow = ws.Cells(ws.Rows.Count, targetCol).End(xlUp).Row
    
    ' Loop through each row starting from row 1 (adjust if your header is in row 1)
    For i = 1 To lastRow
        ' Check if current source cell is bold
        If ws.Cells(i, sourceCol).Font.Bold = True Then
            boldValue = ws.Cells(i, sourceCol).Value
            ws.Cells(i, targetCol).Value = boldValue
        Else
            ' If not bold, crawl upwards to find the first bold cell
            boldValue = "" ' Default to empty if no bold cell found above
            For j = i - 1 To 1 Step -1
                If ws.Cells(j, sourceCol).Font.Bold = True Then
                    boldValue = ws.Cells(j, sourceCol).Value
                    Exit For ' Stop searching once we find the first bold cell
                End If
            Next j
            ws.Cells(i, targetCol).Value = boldValue
        End If
    Next i
    
    MsgBox "Process completed!", vbInformation
End Sub

How to adjust and use this: #

• Worksheet Name: Replace "Sheet1" with the actual name of your worksheet.
• Column Numbers: If your target column isn't B (column 2), update targetCol to the correct column number. The script automatically sets sourceCol to the column right before it.
• Starting Row: If your data starts at row 2 (because row 1 is a header), change the For i = 1 To lastRow line to For i = 2 To lastRow.

Step-by-step to run the macro: #

1. Open your Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer (left pane) > Insert > Module.
4. Paste the code above into the new module.
5. Tweak the worksheet name and column numbers to match your dataset.
6. Press F5 to run the macro, or click the play button in the VBA Editor toolbar.

Important Notes: #

• Always back up your dataset before running macros—once changes are made, they can't be undone with Ctrl+Z.
• If your dataset uses an Excel Table (ListObject), you can modify the code to reference ListColumns instead of column numbers for better compatibility.

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