Extend Selection from the Active cell to last Row i.e Non Blank cell in the same Column (having few blank cells )

[shoun2502]

Hi ,
Actually I was trying to run the subroutine to select a Single Column from the Active cell and Drag the selection to the Last Row (The Single Column and Multiple Rows are having some blank cells which are to be skipped). The code below doesn't give the desired result

I would appreciate if anyone could help to run the subroutine to select the same from the Active Cell

I have tried the below code but it is not giving the desired result.

Sub MyLastRowSamecolumn()
    Dim myCurrentColumn As Long
    Dim myLastRow As Long
    myCurrentColumn = ActiveCell.Column
    myLastRow = Cells.Find(What:="*", After:=Cells(1, 1), _
                        Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, MatchCase:=False).Column
    Range(ActiveCell, Cells(myCurrentColumn, myLastRow)).Select
End Sub

Thanks in anticipation

 

[Akuini]

Try this:

Sub MyLastRowSamecolumn()
Dim c As Range
    Set c = Columns(ActiveCell.Column).Find(What:="*", _
                        Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, MatchCase:=False)
    Range(ActiveCell, c).Select
End Sub

 

[shoun2502]

Thanks Akuini it worked perfectly!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[shoun2502]

Hi akuini.

I was wondering If the same could be done with applying a formula(sum, subtotal) for the single column having values.

Please help me if its possible.

Thanks again

 

[Akuini]

Sorry, my knowledge of excel formula is very basic, so I hope other members can help you.
I also don't understand what you mean.