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
Incorporate Application.InputBox
-
acat -
August 7, 2019 at 5:59 PM -
Thread is marked as Resolved.
-
-
-
-
How do I get it to select that cell, rather than MsgBox
-
You can select a cell with an InputBox like this
Code
Display MoreDim rRng As Range ''///Get A Range Address From The User to Get Number Format From On Error Resume Next Set rRng = Application.InputBox( _ Title:="Select range", _ Prompt:="Select a cell in the column to use", _ Type:=8) On Error GoTo 0 ''///Test to ensure User did not cancel If rRng Is Nothing Then Exit Sub
-
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
Code
Display MoreOption Explicit Sub x() Dim rRng As Range ''///Get A Range Address From The User to Get Number Format From On Error Resume Next Set rRng = Application.InputBox( _ Title:="Select range", _ Prompt:="Select a cell in the column to use", _ Type:=8) On Error GoTo 0 ''///Test to ensure User did not cancel If rRng Is Nothing Then Exit Sub Set rRng = rRng.SpecialCells(xlCellTypeConstants) rRng.Cells(rRng.Cells.SpecialCells(xlCellTypeLastCell).Row, 1).Select End Sub
-
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
Code
Display MoreOption Explicit Sub SelectLastCellwithData() Dim rRng As Range Dim var As Variant ''///Get A Range Address From The User to Get Number Format From On Error Resume Next Set rRng = Application.InputBox( _ Title:="Select range", _ Prompt:="Select a cell in the column to use", _ Type:=8) On Error GoTo 0 ''///Test to ensure User did not cancel If rRng Is Nothing Then Exit Sub Set rRng = rRng.SpecialCells(xlCellTypeConstants) var = Split(rRng.Address, ",") Range(var(UBound(var))).Select End Sub
-
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 -
Well, it works for me. Attach an example workbook.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!