I have been searching through many threads (both Excel and Access) for the solution, but cannot get it to work.
I am running a macro to organize data to be imported to MS Access.
I have an Excel spreadsheet "Customer Routing" which has data copied from a web page, this data is sorted to remove ASCII characters and to split the date cell which is for example 11 Jan (Private),
I can have a few or many records so I work in the range AJ2 to AQ100
I highlight the column "AO1:AO100" split the column with "Text to Columns" then reform the date using =AR1&" "&AS1&" "&AT1 this causes the column to become a text field, I then format the column to "a Date" but that only effects the cells filled with the date, so I end up with a column of Date" and "Text" cells
This causes an error when I import into MS Access as I am trying to import a "Date" and a "Text" into a "Date" field in my DB
I have found some code that finds the last row and selects the range "AJ1:AQ100" it appears to selects false data.
My question is:
Why does it select rows below the last filled row and how do I get it to select only filled rows
Attached is part of the code showing the date cell splitting and the find last row
Geoff
