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

    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

    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

    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

    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

    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:

    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!