Selecting Cell ignoring Hidden Cells

[SanjayGMusafir]

Hi Experts,

I have many worksheet(s) with hidden rows.

Now, I need a vba that may help me do following -

• Select table rows, from Second Row below the Hidden Rows till the Bottom of the Table
• And then, Sort them in Ascending Oder. All such tables start from Column A of the worksheet only.
• Actually I want a general code that I may be able to use with any worksheet/table I want.

When I use "Record Macro" method, it uses specific cell addresses, worksheet & table names. Thus it becomes useless once I hide few more rows. Further, it can't be used on any other worksheet/table.

Please Help

Thanks a lot ?

 

[SanjayGMusafir]

Hi Experts
I am able to write this code, picking ides from various sources. Working fine with me.

Certainly it's a novice's work.

Can we improve this coding, or if there is a better way of getting the intended result.

Please help

Thanks a lot ��

Sub GoHm()
'
' GoHm
'


'
    Dim rngData As Range
    Dim rngKey As Range
    
    'To Select firt visible cell after hidden rows in Frozen Pane
    With ActiveSheet
    
    With Range(.Cells(1, 1), .UsedRange)
    
        With .Offset(ActiveWindow.SplitRow, ActiveWindow.SplitColumn).SpecialCells(xlCellTypeVisible)
            .Cells(2, 1).Select
        End With
        
    End With
    
    End With
    
    'To Reach first cell of the selected row
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    
    'To set First cell of the Range to be sorted
    Set rngKey = Selection
    
    'To select range to sort
    Range(Selection, Selection.End(xlDown)).Select
    
    'The range to be sorted
    Set rngData = Selection
    
    'To sort cells in ascending order
    rngData.Sort Key1:=rngKey, Order1:=xlAscending, Header:=xlYes
    
    ActiveWorkbook.Save
    
End Sub

Hi Experts,

I have many worksheet(s) with hidden rows.

Now, I need a vba that may help me do following -

• Select table rows, from Second Row below the Hidden Rows till the Bottom of the Table
• And then, Sort them in Ascending Oder. All such tables start from Column A of the worksheet only.
• Actually I want a general code that I may be able to use with any worksheet/table I want.

When I use "Record Macro" method, it uses specific cell addresses, worksheet & table names. Thus it becomes useless once I hide few more rows. Further, it can't be used on any other worksheet/table.

Please Help

Thanks a lot 