Excel VBA HELP ! > hide & filter columns based on entries in a range on another sheet

[AidanKing]

Hi All, I'm fairly new to coding (about 1.5 yrs basic coding) and i am stuck with this issue: I have a range "Countries" C13:C33 (in a sheet (sheet called "Selection") where a user can type a pre-determined list of countries "Austria, Spain, France etc etc. Once entered, I need the code to jump to another sheet (called "Company information (1)") and hide all columns that do not contain these countries. The country will be present in the column name, the range of columns is "D2:AU" and can have countries or blanks as column names. Once that step is completed I then need the script to filter the visible row that has the most records for "non-blanks" or "Yes" (all rows will have either "blanks" or "yes" entries only.
I have 3 or 4 versions of the code but this is the most recent. I don't know if it is even much help?

Any help is much apreciated as i've be at this now for hours (& 3 or 4 different iterations/approaches to no avail)

(I currently have the code linked to a command button but this is not critical)

Here is the code:
Private Sub CommandButton3_Click()

Dim rCrit1 As Range, rCrit2 As Range, rRng1 As Range, rRng2 As Range


With Application
.EnableEvents = False
.ScreenUpdating = False


End With
Set rCrit1 = Range("C13:C43")
'Set rCrit2 = Range("C13:C43")


Set rRng1 = Sheets("Selection").Range("C13:C43")
Set rRng2 = Sheets("Company information (1)").Range("A:AU")


With rRng2
For Each Record In rRng1
rCrit1 = rRng2 Then
Rows("rRng2").EntireRow.Hidden = false Criterial:= rRng1.Value, true
.AutoFilter field:=1, Criteria1:="yes"
Application.EnableEvents = True
End Sub

 

[AidanKing]

Resolved