Clear some columns but not all columns and keep headers in all columns
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
|