Excel 2010 VBA how can I delete the values in a column by using the column heading

[DougP]

Is there a way to refer the a column by its name in the top cell. this is also set up as a filter.
the column name is Totals and it is column "C" the heading Totals is in C1 so I don't want to delete that.
I have this code but can it refer to "totals" some how, as it might not be column "C".

    With ActiveWorkbook.Worksheets("Sheet2")
        .Range("C2", .Range("C2").End(xlDown)).Delete
    End With

DougP

 

[PHV]

What about this ?

With ActiveWorkbook.Worksheets("Sheet2")
  For c = 1 To .Cells.Columns.Count
    If .Cells(1, c) = "Totals" Then
      .Range(.Cells(2, c), .Cells(2, c).End(xlDown)).Delete
    End If
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

If you were using the Structured table feature,

[Table1[Total]].ClearContents

If you use Named Ranges based on the values in the TOP ROW,

[Total].ClearContents

or

Range("Total").ClearContents

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

this might also work for you

    With ActiveSheet.UsedRange
        Intersect(Rows(1).Find("Total").EntireColumn, Range(Cells(2, 1), Cells(.Rows.Count, 1)).EntireRow).ClearContents
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}