Macro button to go to the last non-blank cell in column

[adorlytanglao]

I want to have macro form button with a code on it that when click the selected cell will be the last non-empty cell on the Column B. Note B1 is non-empty. because that's where I'm keeping the visibility of the button. Also the Column is pre-filled with data(ID No.) thats why i need the code to check the Column B. Please let me know if there are more information needed.

 

[Andrew Poulsom]

Does this work for you?

Range("B" & Rows.Count).End(xlUp).Select

 

[adorlytanglao]

Thanks for the response. it doesn't work, it is going to the last cell of Column inside the table. My table is extending until row 703 and its selecting B703. But non-empty cell in column B is 417, so it should select B417.

 

[MARK858]

Are your empty cells actually empty or are they actually "" as results of formula?

 

[Andrew Poulsom]

You didn't say that you had a table in your original post. Does this work for you?

Range("B2").End(xlDown).Offset(1).Select

 

[adorlytanglao]

They are empty cells, not results of formula. However, andrew already fixed the formula. I just forgot to mention i have a table.

 

[adorlytanglao]

Just realized something on my requirement. I applied the same formula on a different sheet, apparently some cells on ColumnB are empty and they are between non-empty cells. For example, using the same setup, the button selects B417. However i tried, emptying the B320 and upon clicking the button it went to the said cell.

 

[MARK858]

Try the code below. Change your table name to suit.

Sub ZZZ()
ActiveSheet.ListObjects("Table1").ListColumns(2).Range.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Select
End Sub

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim Rng As Range
    With ActiveSheet.ListObjects(1)
        Set Rng = .Range
        .Resize .Range.Rows("1:2")
        .Parent.Range("B" & .Parent.Rows.Count).End(xlUp).Offset(1).Select
        .Resize Rng
    End With
End Sub

 

[adorlytanglao]

Thank you andrew, it worked.