CODEWITHSUNDEEP
excelvba
Robert Hall
Robert Hall

Reputation: 191

Find header row name

enter image description here

Hi. I have a Table (Table1) with columns named Enq 1, Enq 2 and so on. What i need to do is be able to select that column based on a condition on a cell above and delete the data inside. I have written the code as below but i cant seem to figure out how to select the column using VBA. I can not use Offset as there is a possibility of more rows added in the future

Dim rng As Range: Set rng = ActiveSheet.Range("Removal")
Dim cel As Range
For Each cel In rng.Cells
  If cel = "SELECTED FOR REMOVAL" Then
    Range("Table1[Enq 2]").Select
    Selection.ClearContents
  End If
Next cel

Thanks

Upvotes: 2

Views: 201

Answers (2)

JvdV
JvdV

Reputation: 75870

Alternatively, you could try:

Sub Test()

With ThisWorkbook.Sheets("Sheet1") 'Name accordingly, avoid activesheet!
    For Each col In .Range("Table1").Columns 'Change tablename accordingly
        If .Cells(1, col.Column) = "SELECTED FOR REMOVAL" Then
            col.Clear 'Or ClearContents if you need that
            'Exit For 'Might want to use this to prevent a full loop if required
        End If
    Next col
End With

End Sub

Upvotes: 1

AAA
AAA

Reputation: 3670

It's probably better to use a For..Next loop here, to get the column to remove:

Dim ws  As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim rng As Range: Set rng = ws.Range("Removal")
Dim i as Long
For i = 1 To rng.Columns.Count
  If rng.Cells(1, i).Value = "SELECTED FOR REMOVAL" Then
    ws.ListObjects("Table1").ListColumns(i).Range.ClearContents
  End If
Next i

Upvotes: 2

Related Questions