问查找特定列并清除其下的所有行

EN

Option Explicit

Sub test()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim Col As Double
    On Error Resume Next 'next line throws error if "Course Info" was not found
    Col = Application.WorksheetFunction.Match("Course Info", ws.Rows(1), 0)
    On Error GoTo 0 'always re-activate error reporting

    If Col <> 0 Then 'only do it if "Course Info" was found
        Dim Cell As Range
        For Each Cell In ws.Range(ws.Cells(2, Col), ws.Cells(ws.Rows.Count, Col).End(xlUp))
            Cell.Value = Application.WorksheetFunction.Clean(Cell.Value)
        Next Cell
    End If
End Sub

Option Explicit
Sub Test()

    Dim Rng As Range, Col As Long, cell As Range, LastRow As Long, LastCol As Long

    With ShData
        LastRow = .Range(.Rows.Count, 2).End(xlUp).Row
        LastCol = .Range(1, .Columns.Count).End(xlToLeft).Column
        Col = .Rows(1).Find("Course Info").Column 'this is to find the column number
        Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col))
    End With

    For Each cell In Rng
        cell = Application.WorksheetFunction.Clean(cell)
    Next cell

End Sub