Find Last non blank cell in a column and select that cell and cells in next 4 columns of that row. RRS feed

  • Question

  • I want to use a button to look for the last Non-blank cell of a Range and to select that cell and the cells in the 4 columns next to it to delete those values.

    Example: If the Last non-blank cell of "H1:H260" is on row 25 then I need to select cells "H25:L25" and delete those values.

    I use this formula for finding the first "Blank" cell of a Range but I can't figure out how to get the button to select the next cell up that does have info. and the cells in the 4 columns beside that cell to delete that info.: 

    Sub Find_First_Blank_Cell()
        x = Cells(Rows.Count, "H").End(xlUp).Row
        nar = Range("H1:H260" & x).Find("").Row
        Cells(nar, "H").Select
    End Sub

    Please help a beginner,


    Monday, December 19, 2016 10:15 PM

All replies

  • Hi FoosMaster,

    please refer the example mentioned below. based on that you can try to develop your own code.

    as you mentioned above I create 1 button and on the click event of button I wrote the code.

    For example here I am using Range (A1:A10).

    so when you click on the button it will find the last used cell in range (A1:A10). then select that and next four cells in the same row.

    Private Sub CommandButton1_Click()
    Dim WS As Worksheet
    Dim LastCell As String
    LastCell = Range("A1:A10").SpecialCells(xlCellTypeLastCell).Row
    ActiveSheet.Range("A" & LastCell & ": E" & LastCell).Select
    End Sub


    you can see that last used cell is A9. so it select the range A9:E9.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Tuesday, December 20, 2016 1:59 AM