Using this select case only as a means to explain what I want some code to do. Actual code to be whatever is most efficient.

Case has the column Header and the column letter is not part of the header name, I put it in there for reference only, real header name is like... Answer Sports?

Case Else are the target columns to clear, keeping header for ALL columns.

Thanks,
Howard

From column A to column O on Sheet3...

Select Case columnHeading
Case "Answer Movie? C", "Answer Sports? F", "Answer Geography?
I", "Answer Math? L", "Answer Art? O"
'Do nothing
Case Else
'ClearContents in columns (A B D E G H J K M N) row 2 on down to variable end
End Select

Hi Howard,

Am Tue, 4 Jul 2017 14:29:22 -0700 (PDT) schrieb L. Howard:

Using this select case only as a means to explain what I want some code to do. Actual code to be whatever is most efficient.

Case has the column Header and the column letter is not part of the header name, I put it in there for reference only, real header name is like... Answer Sports?

Case Else are the target columns to clear, keeping header for ALL columns.

try:

Sub ClearColumns()
Dim LRow As Long
Dim myStr As String
Dim i As Integer

myStr = "Answer Movie?,Answer Art?,Answer Sports?,Answer Geography?,Answer Math?"

With Sheets("Sheet3")
LRow = .UsedRange.Rows.Count
For i = 1 To 15
If InStr(myStr, .Cells(1, i)) = 0 Then
.Range(.Cells(2, i), .Cells(LRow, i)).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

On Tuesday, July 4, 2017 at 3:04:34 PM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 4 Jul 2017 14:29:22 -0700 (PDT) schrieb L. Howard:

Using this select case only as a means to explain what I want some code to do. Actual code to be whatever is most efficient.

Case has the column Header and the column letter is not part of the header name, I put it in there for reference only, real header name is like... Answer Sports?

Case Else are the target columns to clear, keeping header for ALL columns.

try:

Sub ClearColumns()
Dim LRow As Long
Dim myStr As String
Dim i As Integer

myStr = "Answer Movie?,Answer Art?,Answer Sports?,Answer Geography?,Answer Math?"

With Sheets("Sheet3")
LRow = .UsedRange.Rows.Count
For i = 1 To 15
If InStr(myStr, .Cells(1, i)) = 0 Then
.Range(.Cells(2, i), .Cells(LRow, i)).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.
--

Hi Claus,

The columns in MyStr are the columns I want to remain untouched. All the other columns cleared.

The columns are sequenced in this manner

Name Movie "Answer Movie?" Name Sports "Answer Sports?" etc. etc.

Five columns are named "Name" and five are the category (Movie, Sports etc.) and five are Answer xx? columns.

All the Answer xxx? column to remain intact with formulas in them.

I tried adjusting the code but the InStr was picking up Movie and Answer Movie as an example.

Howard

On Tuesday, July 4, 2017 at 5:38:14 PM UTC-7, L. Howard wrote:
On Tuesday, July 4, 2017 at 3:04:34 PM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 4 Jul 2017 14:29:22 -0700 (PDT) schrieb L. Howard:

Using this select case only as a means to explain what I want some code to do. Actual code to be whatever is most efficient.

Case has the column Header and the column letter is not part of the header name, I put it in there for reference only, real header name is like... Answer Sports?

Case Else are the target columns to clear, keeping header for ALL columns.

try:

Sub ClearColumns()
Dim LRow As Long
Dim myStr As String
Dim i As Integer

myStr = "Answer Movie?,Answer Art?,Answer Sports?,Answer Geography?,Answer Math?"

With Sheets("Sheet3")
LRow = .UsedRange.Rows.Count
For i = 1 To 15
If InStr(myStr, .Cells(1, i)) = 0 Then
.Range(.Cells(2, i), .Cells(LRow, i)).ClearContents
End If
Next
End With
End Sub


Regards
Claus B.
--

Hi Claus,

The columns in MyStr are the columns I want to remain untouched. All the other columns cleared.

The columns are sequenced in this manner

Name Movie "Answer Movie?" Name Sports "Answer Sports?" etc. etc.

Five columns are named "Name" and five are the category (Movie, Sports etc.) and five are Answer xx? columns.

All the Answer xxx? column to remain intact with formulas in them.

I tried adjusting the code but the InStr was picking up Movie and Answer Movie as an example.

Howard

Hi Claus,

I was able to make it work with this.
Thanks for help getting me there.

Howard

Sub ClearColumns()
Dim LRow As Long
Dim myStr As String
Dim i As Integer

myStr = "Name, Movie, Sports, Geography, Math, Art"

With Sheets("Sheet3")

LRow = .UsedRange.Rows.Count

For i = 1 To 15

If InStr(myStr, .Cells(1, i)) 0 Then

.Range(.Cells(2, i), .Cells(LRow, i)).ClearContents

End If

Next

End With
End Sub

Hi Howard,

Am Tue, 4 Jul 2017 21:02:25 -0700 (PDT) schrieb L. Howard:

I was able to make it work with this.
Thanks for help getting me there.

sorry that I misunderstood your problem.

Try:

Sub ClearCols()
Dim LRow As Long
Dim i As Integer

With Sheets("Sheet3")
LRow = .UsedRange.Rows.Count
For i = 3 To 15 Step 3
.Range(.Cells(2, i), .Cells(LRow, i)).ClearContents
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016