Find last row copy and paste into access

%3CLINGO-SUB%20id%3D%22lingo-sub-2046072%22%20slang%3D%22en-US%22%3EFind%20last%20row%20copy%20and%20paste%20into%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046072%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20searching%20through%20many%20threads%20(both%20Excel%20and%20Access)%20for%20the%20solution%2C%20but%20cannot%20get%20it%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20running%20a%20macro%20to%20organize%20data%20to%20be%20imported%20to%20MS%20Access.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20spreadsheet%20%22Customer%20Routing%22%20which%20has%20data%20copied%20from%20a%20web%20page%2C%20this%20data%20is%20sorted%20to%20remove%20ASCII%20characters%20and%20to%20split%20the%20date%20cell%20which%20is%20for%20example%2011%20Jan%20(Private)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20have%20a%20few%20or%20many%20records%20so%20I%20work%20in%20the%20range%20AJ2%20to%20AQ100%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20highlight%20the%20column%20%22AO1%3AAO100%22%20split%20the%20column%20with%20%22Text%20to%20Columns%22%20then%20reform%20the%20date%20using%20%3DAR1%26amp%3B%22%20%22%26amp%3BAS1%26amp%3B%22%20%22%26amp%3BAT1%20this%20causes%20the%20column%20to%20become%20a%20text%20field%2C%20I%20then%20format%20the%20column%20to%20%22a%20Date%22%20but%20that%20only%20effects%20the%20cells%20filled%20with%20the%20date%2C%20so%20I%20end%20up%20with%20a%20column%20of%26nbsp%3BDate%22%20and%20%22Text%22%20cells%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20causes%20an%20error%20when%20I%20import%20into%20MS%20Access%20as%20I%20am%20trying%20to%20import%20a%20%22Date%22%20and%20a%20%22Text%22%20into%20a%20%22Date%22%20field%20in%20my%20DB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20found%20some%20code%20that%20finds%20the%20last%20row%20and%20selects%20the%20range%20%22AJ1%3AAQ100%22%20it%20appears%20to%20selects%20false%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%3A%3C%2FP%3E%3CP%3EWhy%20does%20it%20select%20rows%20below%20the%20last%20filled%20row%20and%20how%20do%20I%20get%20it%20to%20select%20only%20filled%20rows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20part%20of%20the%20code%20showing%20the%20date%20cell%20splitting%20and%20the%20find%20last%20row%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGeoff%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2046072%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046235%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20last%20row%20copy%20and%20paste%20into%20access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923561%22%20target%3D%22_blank%22%3E%40GeoffK78%3C%2FA%3E%26nbsp%3BHave%20you%20considered%20using%20PowerQuery%20to%20do%20the%20data%20gathering%2C%20cleaning%20and%20transformation%20(i.e.%20splitting%20and%20data-typing)%3F%20It's%20often%20easier%20than%20VBA.%20Which%20web-site%20are%20you%20copying%20data%20from%3F%20Can%20you%20upload%20an%20example%20file%2C%20showing%20the%20data%20copied%20from%20the%20web%20and%20how%20the%20end%20result%20should%20look%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

1 Reply

@GeoffK78 Have you considered using PowerQuery to do the data gathering, cleaning and transformation (i.e. splitting and data-typing)? It's often easier than VBA. Which web-site are you copying data from? Can you upload an example file, showing the data copied from the web and how the end result should look like?