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!

 

[Fluff]

How about

Sub movehidecols()

   Columns(4).Cut
   Columns(1).Insert
   Columns("C:D").Hidden = True
End Sub

 

[unknownymous]

Thanks Fluff for quickly answering this. Each columns is actually interchangeable, is there any code where we can specify the header name like instead of Columns (4) we'll use "Score" and the like? Let me know any of your thoughts.

 

[Fluff]

How about

Sub MoveHideCols()

   Dim Fnd As Range
   
   Set Fnd = Range("1:1").Find("Score", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Fnd.EntireColumn.Cut
      Columns(1).Insert
   End If
End Sub

 

[unknownymous]

This works! Thanks Fluff

 

[Fluff]

Glad to help & thanks for the feedback

 

[unknownymous]

Last question Fluff..what if I want to hide Day & Score columns as well, how do you show that in codes?

 

[Fluff]

How about

Sub MoveHideCols()

   Dim Fnd As Range
   
   Set Fnd = Range("1:1").Find("Score", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Fnd.EntireColumn.Cut
      Columns(1).Insert
      Range("A:A,C:C").EntireColumn.Hidden = True
   End If
End Sub

 

[unknownymous]

Thanks Fluff..I forgot that the columns are interchangeable. Can we be more specific based on header names instead of using "A:A" & "C:C". Thank you in advance

 

[Fluff]

If you want to hide the columns by header, you'd need to replicate the first line to find the various headers.