VBA to hide columns based on Criteria and change a column's position

[unknownymous]

Hello Guys,

I'm trying to write a macro that will hide columns and change a columns position based on criteria. You refer to sample data below:

*What I need here is to hide "Sports" & "Day" columns

Name	Day	Sports	Score
Ben	1	Basketball	15
Sean	2	Basketball	21
Ana	1	Tennis	4

<tbody>
</tbody>

*Another, how will I force to move the "Score" column to the first column (Row A)

Thanks for any help!

 

[unknownymous]

It work but I'm having a problem on this part "Range("A:A,C:C").EntireColumn.Hidden = True" as I have added more columns (now range from A1 to AH1) and sometimes the column positions differ. Thanks in advance for the help.

 

[Fluff]

In what way are you "having a problem"?

 

[unknownymous]

I'm just having a problem on hiding the columns part. Example, the Sports column is in C1 but since the column is movable sometimes the macro hide the wrong column. So I was thinking instead of using the "Range("C:C")" maybe we can replace other code that specify the column name.

______________________________

Sub MoveHideCols()

Dim Fnd As Range

Set Fnd = Range("1:1").Find("Sports", , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
Range("C:C").EntireColumn.Hidden = True
End If
End Sub

______________________________

Also, I have added columns like Time, Hours, Place, Coach & Result that I need to hide as well. Is there any way where I can hide all of these by avoiding using repetitive codes.

Let me know what you think. Thanks!

 

[Fluff]

You'll need to put it in a loop, along the lines of

   ary = Array("Time", "Hours")
   For i = LBound(ary) To UBound(ary)
      Set Fnd = Range("1:1").Find(ary(i), , , xlWhole, , , False, , False)
   Next i

 

[unknownymous]

As always, thanks Fluff!

 

[Fluff]

Glad to help & thanks for the feedback