Verify Column Letter Selected

[jutlaux]

I have a inputbox that prompts the user to enter a column letter so that a
script can run on all data in that column. The problem I have is that user
some times fat fingers this and enters things like "A3" or "A3E" Is there
anyway to check to make sure what the user inputs is a valid column letter?

 

[Dave Peterson]

Don't use inputbox, use application.inputbox

dim myRng as range
set myrng = nothing
on error resume next
set myrng _
= application.inputbox(Prompt:="select a single cell", type:=8).cells(1)
on error goto 0

if myrng is nothing then
'user hit cancel, what should happen?
else
set myrng = myrng.entirecolumn
or maybe...
set myrng = intersect(myrng.parent.usedrange, myrng.entirecolumn)
'do something
end if

 

[Jacob Skaria]

Either use Application.Inputbox Type:=8 so that the user can select the range.

OR

Get the column as numbers and refer it as
Cells(row,column)

OR

If you are looking at last filled column use the below which will return
the last used column in row 1

lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

 

[Rick Rothstein]

If you are looking at last filled column use the below which will return

the last used column in row 1

lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Or use this code which will return the last filled in column no matter what
row it occurs in...

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column