Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear some columns but not all columns and keep headers in all columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear some columns but not all columns and keep headers in all columns
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear some columns but not all columns and keep headers in all columns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear some columns but not all columns and keep headers in all columns
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make headers for columns in which their are 6 columns? | Excel Worksheet Functions | |||
How do I slant the headers of the columns? | Excel Worksheet Functions | |||
Line up headers and columns | Excel Discussion (Misc queries) | |||
Clear columns or Drap Columns | Excel Programming | |||
copy over just columns and headers | Setting up and Configuration of Excel |