DataType for a Range

[RaunLGoode]

Is it possible to return the data type for a range? In this case the range is a column. If possible, what would be the code to do so? I can see this would be problematic if there was more than one data type in the range.
Thanks,

 

[Ranman256]

you SHOULDNT have more than 1 data type in a column. This is data 101.
In excel you could scan the column and find the bad ones. The code below scan the column and
converts everything to text:

'this is ONLY for items in every row
Sub Cvt2Text()
While ActiveCell.Value <> ""
    If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   ActiveCell.Offset(1, 0).Select
Wend
End Sub

 

[RaunLGoode]

I have a situation where Columns with date data require a specific format. I have to take client spreadsheets and clean up data of various data types. In this instance, the data type is date, but I wanted to create case statements, based on the data type of the range, to locate and resolve data issues. In this situation it is dates, but I think this could be adapted to resolve other issues as well. More conventional methods to control data integrity, like locking cells and using validation lists are not options because the customer needs the ability to cut and paste data.