Find last row, copy, paste works but a little help with cleaning up the code

Hi there,

So I've spent most of this evening learning VBA to expand my knowledge. I am trying to do a simple everyday task and not sure if there is a quicker way of doing this...

1) Data is in Cell A2 onwards
2) Formula is in B1 and C1
3) Formula is to concatenate B1 and A1 to go down to the bottom of the data set in Cell A
4) Column C is to do the same
5) Copy Column C data into Column B (start at the end of the data set) and then pastevalues
6) Copy and paste the data into a notepad and then save.

I think I'm using to much LastRow / FindLastRow etc... but I can't seem to re-use them as it doesn't seem to re-look at the data set again to copy the new information or view the next lastrow...
Also I've noticed when it copies and pastes it repeats the paste of B2 - B6 (as an example) but in the notepad it copies fine... The issue is that I will probably have around 30-40 thousand rows of data in cell B before I even copy the data in cell C so it could take a little bit of time...

I've blocked out the copy/paste and save to note pad as was testing, this is working fine, although I am trying to read up how to actually save it to a desktop. Eventually I will be saving it to a shared location but will also want to label it to the date (YYYYMMDD - Name of file.txt)

Is there any help on this please or any way to simplify it.

Please Login or Register  to view this content.

Thanks

Originally Posted by mehmetcik

Please Login or Register  to view this content.

Wow thanks, looks alot neater i will gove this a try tomorrow as ive only just turnt my computer off and let you know how i get along. Thank you again

Please Login or Register  to view this content.

Morning, thank you for your help, the clean up code has lost a few elements when trying to run this.
There is a formula in both B1 and C1 that needs to go to the bottom of the data set. The cleaned up version only copies the formula in B1 and not the one in C1. Also when it opens the notepad, it doesn't copy the data in from B1 (it may reads 1 character of 1 line and sometimes brings a full second line back).
For me the most important part was being able to do the following:

1) Copy the formula in B1 to the bottom of the data set (formula is $B$1&$A1)
2) Find the last empty row in column B where the formula has been copied
3) Copy the formula in C1 to the bottom of the data set (formula is $C$1&$A1)
4) Select all the values from B2 downwards
Also when copying to notepad, it does seem to copy the first formulas in the B1 - B5 (as an example).

I've attached the spreadsheet (it's only a test version as the main one will have lots of data in cell A) if it helps. I was trying to understand if there was a way to clean up the code or if my version of the code had to many different lastrow elements.

Hey thank you for your help mehmetcik - the code clean up was great. I made a few more tweaks so that I was able to save this automatically. I removed the notepad option and ended up opening a new worksheet and saving the values there and then saving that as a .txt file which works alot better.