Incorporate Application.InputBox

  • Want to improve below code* with an InputBox which would allow to click on column header letter to pick the column, rather than having column hard coded (F in this case). As long as it will be locating last cell with data then no need to hard code the range either, but user input needed only for column
    *it locates last cell with data ignoring formulas which return blanks



    Code
    1. Sub LastRowIgnoreFormula()
    2. Cells(Application.Evaluate("MAX(IF(F6:F1005<>"""",ROW(F6:F1005)),0,1)"), "F").Select
    3. End Sub
  • Here's an example to find last row with data in a column.

    Code
    1. Sub FindData()
    2. Dim lastRow As Long
    3. lastRow = ActiveCell.EntireColumn.Find(what:="*", LookIn:=xlValues, searchdirection:=xlPrevious).Row
    4. MsgBox "Last row with data: " & lastRow
    5. End Sub

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • You can select a cell with an InputBox like this


  • Maybe this will clarify: I am looking to improve code in my original post above with an InputBox which would allow to just click on a column header letter and then the code should select the last cell with data in that column automatically, while ignoring cells that contain formulas but return blanks
    Alternatively, without the use of InputBox, for code to just use active cell's column to accomplish the same

  • You would need to select a cell in the column.


    Try this code


  • this selects a cell after last that has formula in column I select
    whereas
    code should select the last cell with data in that column, while ignoring cells that contain formulas but return blanks

  • It actually selects the last cell in the column selected that has been used that contains Text or Formula.


    Try this amendment



  • Nope
    Selecting F2 when prompted
    After OK it selects A6:B & down to last row with data
    These are the columns that happen NOT to have formulas in them, unlike F