Miscellaneous Exercise 2

'Check filter is applied on the sheet or not if not apply filter on it
Sub autofilteron()

Sheet3.Activate
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("1:1").AutoFilter
End If

End Sub
'put auto filter off
Sub autofilteroff()

Sheet3.Activate
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If

End Sub
'hides all Filters except column 2
Sub HideArrows()

Dim C As Range
Dim i As Integer

Sheet3.Activate
i = Cells(1, 1).End(xlToRight).Column

For Each C In Range(Cells(1, 1), Cells(1, i))
    If C.Column <> 2 Then
        C.AutoFilter field:=C.Column, Visibledropdown:=False
    End If
Next

End Sub

'Count Visible Rows
Sub CountVisRows()

Sheet3.Activate
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
MsgBox rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 _
    & " of " & rng.Rows.Count - 1 & " Records"

End Sub


'Create a filter from B to G column and put filter on Course
'Name'"Central" and Permanent Address="Pen Set"
Sub autofiltertest1()

Sheet3.Activate
Range("B:G").AutoFilter field:=4, Criteria1:="Central"
Range("b:g").AutoFilter field:=6, Criteria1:="Pen Set"

End Sub


'If you set an object variable to a Range object, you can easily manipulate the range by using the variable name.

'The following procedure creates the object variable myRange and then assigns the variable to range A1:D5 on Sheet1 in the active workbook. Subsequent statements modify properties of the range by substituting the variable name for the Range object.

Sub Random()

    Sheet3.Activate
    Dim myRange As Range
    Set myRange = Worksheets("Sheet3").Range("A1:D5")
    myRange.Formula = "=RAND()"
    myRange.Font.Bold = True
   
End Sub