CODEWITHSUNDEEP
excelvba
William Pieters-Verbiest
William Pieters-Verbiest

Reputation: 3

find specific column and clean all rows under it

I have an excel sheet with a random amount of columns and rows (amount of columns/rows changes every time, as well as the location of each column) where I need to find a certain column with title "Course Info", and then use the CLEAN function on all rows / cells in that column (except for the title row ofcourse).

I have the code for the clean function:

Set Rng = ShData.Range("AB2:AB" & LastRow)

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

Problem here is that Rng is set to column AB, which isn't always that column. I have also made a LastRow & LastCol code to count the amount of rows and columns, but beyond this I'm stuck.

LastRow = ShData.Range(2, Rows.Count).End(xlUp).Row
LastCol = ShData.Range(1, Cols.Count).End(xlToLeft).Column

Upvotes: 0

Views: 56

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Use the WorksheetFunction.Match method to get the column number.

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

Upvotes: 0

Damian
Damian

Reputation: 5174

Here:

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

Upvotes: 0

Related Questions