Select Blank Cell in a Range

    I want to select a Blank Cell in a range


    Please see the attached sheet. In the sheet I would like to select Cell I15.


    I am aware of selecting with the formula

    Code
    Range("C15").Offset(, 6).Select


    But what I need is to select with XL(end) formula.


    I have done some work that can be seen in the module, however I am even placing my requirments here.


    Re: Select Blank Cell in a Range


    You cannot use End(xlUp) because that will find the first Cell with a value. The only way would be if you knew which cell you wanted, in which case you would just select that cell..


    This might be what you want


    Code
    Private Sub test()
    Dim Rw As Long, Col As Long
    Col = Cells(8, 4).End(xlToRight).Column
    Rw = Cells(Rows.Count, 3).End(xlUp).Row
    MsgBox Cells(Rw, Col).Address
End Sub


    You rarely need to select a Range in VBA, so it might be better to tell us what you think you need to do this for.


    Also, the code should be in a Standard module not the Worksheet.

    Re: Select Blank Cell in a Range


    I need to select the blank cell for formula.


    If that is not possible as the cells are blank then I have something in my mind that I am not able to figure it out how to go about would appreciate if helped.


    Idea is to select range D10 : I15


    My thought is


    1. Finding the number of columns from D8 to End of the row (In this case is I8). The number of counts would give 5 (column E8, F8,G8, H8, I8)


    2. Then counting number of rows from row number 10 to end from C10 to end (In this case the counts would be 5) (row 11, 12, 13, 14, 15)


    3. Now we can apply the formula


    Code
    set aa =  Range ("D10").offset(5)


    This will select cell D15 , As we know the number of rows is "5" so offset is 5, as per my idea in point 2


    Then another code


    Code
    set bb = range (aa.address).offset(, 5)


    Same here as we know the number of columns is "5" so offset is 5, as per my idea in point 1


    That should select cell I15


    This can be then put in my formula as below


    Code
    Range("D10:"  &  bb.address).select


    Hope I am able to express myself properly

    Re: Select Blank Cell in a Range


    Here's one approach that works with your sample workbook. An alternative would be to count how many cells in column C and row 8 contain values:


    Code
    With Range("C8")
    .Offset(2, 1).Resize(.End(xlDown).End(xlDown).Row - .Row - 1, .End(xlToRight).End(xlToRight).Column - .Column).Select
End With

    Re: Select Blank Cell in a Range


    The section of my code finds the last cell in the Table. I have no idea what the rest of your code is supposed to do.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login