Tuesday, February 17, 2015

How to Select all the Visible Cells in a Column of Filtered Range and Fill with a Desired Value

Macro to Select all the Visible Cells in a Column of Filtered Range and Fill with a Value
Sub Select_All_Visible_Cells_In_Filtered_Range()
Dim Rng As Variant
Dim WS As WorkSheet
Set WS=ActiveSheet

    WS.Range("A1").Offset(1, 0).Select
    WS.Range(Selection, Selection.End(xlDown)).Select
   Selection.SpecialCells(xlCellTypeVisible).Value = "MyValue"
End Sub

Brief Explanation:
Suppose we have a data , where we have applied the filter on < Column A > then we want to select the all the Visible cells in <Column A> excluding the header , then we have to fill a desired value in the selected Visible Cells.

To do this we can use the above macro.

Sample Filtered Data :
Here the the data is filtered on < Coluumn A >

Output data :
Here the Visible Cells in < Column A > has been selected and filled with < desired value >


Thanks,TAMATAM

How to Select First Visible Cell in a Column of a Filtered Range

Macro to Select the First Visible Cell in a Column in a Filtered Range
Sub Slect_First_sVisible_Cell_In_Filtered_Range()
Dim Rng As Variant
    
    Range("B1").Offset(1, 0).Select 'Excludes Header in Selection
    'Selecting the All Visible Cells in Column B
    Range(Selection, Selection.End(xlDown)).Select
    'Selecting the First Visible Cells in Column B
    Selection.SpecialCells(xlCellTypeVisible).Cells(1).Select
    'MsgBox ActiveCell.Value
    
End Sub

Brief Explanation:
Suppose we have a data , where we have applied the filter on < Column A > then we want to select the first Visible cell in <Column B> excluding the header , as shown below ,we can use the above macro :





Thanks, Tamatam

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog