Doing my nuts trying to learn all this stuff! Ok I think this is another easy one.
I want to copy populated cells between F:CC. Currently I'm using the following...
But really i only want it to select down the last populated row. I think I've worked out to get it to count this...
Please Login or Register to view this content.
But I'm not sure how to put it all together so that it works :sPlease Login or Register to view this content.
Can anyone help?
Hi there,
As long as there's some data, you can use this as it will find the last row where ever it resides in columns F through to CC:
Note, you don't have have to select a to copy it - just change the .Select to .CopyPlease Login or Register to view this content.
HTH
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Thank for your advice. I have a problem, if I may explain:
The data in which I am wishing to select in this sheet is copied in (as values) from another sheet to F:CB, in batches of 240 & then filtered. The data has gaps, so after it is filtered only populates an unspecified amount of rows downwards (Hence why I am looking to only select populated rows). Column CC has a formula dragged down to CC1000 that collates a string from various cells within these columns. The code you have advised now selects F2:CC241 if I have copied in one batch, F2:CC481, if two, etc. So I am wondering if there is a way to overcome this as it seems to believe that the cells are populated, when in fact they are not? :s
It's finding the last row in columns F through to CC so whatever that row number is will be what's selectedSo I am wondering if there is a way to overcome this as it seems to believe that the cells are populated, when in fact they are not?
If you only want to find the last row in column F as your original post stats, use this:
If you can't resolve the issue, attach a work book with one tab showing how the data currently looks and another tab showing how'd you like it to look and I'll take a look (can't promise anything).Please Login or Register to view this content.
Robert
COPY HELP.xlsb
Hey,
I have stripped out some stuff to a new sheet which i have attached above.
I input a batch of data into Sheet 1, which then needs to be added to the current data in Sheet 2. The first time I ran the code it pasted it perfectly in row 49 (note the change in date in F). However, when I run it again it believes the last populated row is 288 instead of 91. Hence my problem.
Thanks for your help!
There are blanks i.e. "" in the cells which, though they're not visible, are regonised as valid characters which the code is including when determining what the last row is in both Sheet1 and Sheet2.it believes the last populated row is 288 instead of 91
If you delete these rows the code should work fine.
Robert
Ok so the issue is the "" which is copied over. I had feared this was the case.
So, either i need to:
Clear Contents in F:CB under the cells I want to copy over in sheet 1 after I filter the data,
or,
In sheet2, after the paste section from the code I need to clear contents from under the last cell I want it to see, in order remove anything unwanted.
What do you suggest the best way of doing this is?
Thanks again.
As the cells contain #REF! errors, blanks, nulls and single spaces I'd have to say manuallyWhat do you suggest the best way of doing this is?
Robert
So, perhaps it would be possible in sheet 1 to find the first row in CC that contains an error, then clear contents F:CB from that row downwards?
No, as each row in column CC of Sheet1 in the attached has a #REF! errorSo, perhaps it would be possible in sheet 1 to find the first row in CC that contains an error, then clear contents F:CB from that row downwards?
Can we use column F i.e. check each cell from cell F2 down to the first non data cell (F44 in the posted example) and use that for the range to be copied?
Robert
That would be ideal if possible? :-)
OK, post back re how this goes:
Regards,Please Login or Register to view this content.
Robert
Thanks so much. I have altered the code for my sheets as follows:
In reality Sheet1 and Sheet2 exist in different workbooks:Please Login or Register to view this content.
Sheet1=
WB: FLAT RATINGS (FORM); Sheet: "COPY SHEET (STAGE3)"
Sheet2=
WB: Race Records; Sheet: "MASTER"
But when I input these into the code, it returns a bug at:
[CODE]Sheets("COPY SHEET (STAGE3)").Range("F2:CC" & lngEndRow).Copy[CODE/]
can't really work out why though :-s
Assuming both workbooks are open within the same session of Excel and both are macro-enabled workbooks (i.e. both their extensions are "xlsm"), try this:
RobertPlease Login or Register to view this content.
Both are open in the same session but they are .xlsb
Returns a bug at:
Please Login or Register to view this content.
Not sure as it works for me
Ensure there's no extra spaces in either the workbook name or sheet tab and try again?
It's late here in Oz so I'm about to sign off - good luck!!
Thank you so much for your help! I'll let you know if I sort it :-)
No problems - let me know either way as I'm curious. I notice you're using a Mac - I wonder if that's the issue as some of code maybe different?
There was a gap, and now it works beautifully! Thank you once again!
Ah, that's good to hear
If you could please mark the thread as closed when you get a moment it would be appreciated.
Regards,
Robert
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks