Help with shortening and simplifying a code and also ignore cells with ""

[galvinpaddy]

Morning happy campers,

Code is currently:

Please Login or Register  to view this content.

Works great on its own...
I have a couple of problems however
1) within both ranges mentioned, there can be a number of cells that are blank ("") as a result of an IF statement
2) I will be running this code on a workbook that has 12 sheets (K1 through to K12) and all will be pasting data into sheet named 'Collation', so would the above not slow it down with all the '.Select' commands?

Now i know that the above code will paste data straight into A1, then go to end of range and down one cell, so when the code is reading from multiple sheets i would change it to

Please Login or Register  to view this content.

(unless of course there is a better way?)

I would like to either ignore cells that contain "" when copying the data to paste into 'Collation'
or
Once all the sheet ranges have been pasted into 'Collation' I need to find a way to highlight the whole range and delete the cells that are blank - I had thought about -> on collation sheet, my data would be pasted into columns A:F, so if I put in cell G1 -

Please Login or Register  to view this content.

then copied that down for as many cells have data in A, copy all sheet, paste values, set filter on, select Column G, filter by 'delete', delete all rows, clear filter and job done.
But surely there is a cleaner and quicker way?

Anyway, thanks in advance!

Regards, galvinpaddy

[AB33]

Please Login or Register  to view this content.

[romperstomper]

Do all the cells in the copy ranges contain formulas?

[AB33]

If you a formula which return null string

Please Login or Register  to view this content.

[romperstomper]

But you'd never copy them using this:

Please Login or Register  to view this content.

since you're only copying constants.

[AB33]

Rory!
Thanks!
I just added a new line with out removing the constants.
Replace this line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[galvinpaddy]

Hi thanks for that, but-
Does 'Special Cells' ignore all cells with formulas?
On my sheet Range K5:R22,K24:K40

[galvinpaddy]

Rory!
Thanks!
I just added a new line with out removing the constants.
Replace this line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Thanks, have done that and it copies everything, so, whats the best way now to get rid of the blank cells that were "" before being copied?

[AB33]

It ignores blank cells, but not cells with null string as they are not blanks- they have values but with empty string. So if these cells are copied, but you do not want them, you should first copy all cells(Including cells with null string) and then the last line should delete all rows which have null string in column A.

This is the code

Please Login or Register  to view this content.

[galvinpaddy]

Ok, i've loaded a sample workbook with my exact layout and content.

When i run the code on that sheet it copies everything - i should have said earlier that the full range of K5:R22,K24:R40 may not be filled with data, the majority of it could be ""

[AB33]

Okay!
I think specialcells does not work with multi columns. When I test it, I used a single column (K).
Columns A-C are not blanks, but column D is, so you can use to delete the blanks.

Please Login or Register  to view this content.

Other alternatives are loops and filter to delete blanks in column A.

[galvinpaddy]

absolute quality, the code you gave works a treat
Can i assume that i would simply need to repeat the code as below for it work across multiple sheets?

Please Login or Register  to view this content.

[AB33]

What do you mean by across sheets? You are not copying from the same sheet to other sheets? Alternative code is attached.
You can copy as many columns as you wish. I use filter to delete blanks in column A, but you need to have a header in row 1.

[galvinpaddy]

by many sheets i refer to the original post i made where i said i have 12 in total (K01-K12) within the same workbook.
I have tried using the code i posted and it looks as though it works :D

[galvinpaddy]

Have also tried the last sample you posted, which also works bang on - thank-you very much for your support!!
some well deserved rep given

Regards

[galvinpaddy]

good morning happy campers,

Apologies, I need to revive this thread
Im using the suggestion from AB33 -> The code works great - thanks a lot, however, I cannot use it once the workbook is shared as when doing so I get the following error;

cannot copy or move array entered formulas or data tables in a shared workbook

I have already been though my workbook and removed all Array formulas and this formula above is the only thing left that I think is causing the error, because it is essentially setting a filter, then deleting the not required lines.

I can't un-share the sheet as it will be open by multiple users at the same time, so, is there any way the code can be adapted to suit working on shared workbooks?

Kind regards

galvinpaddy

[galvinpaddy]

Morning happy campers,

Still trying to get my head round this one, spent a bit of time on this last night and i think i need to either:
* amend how the code copies, by this i mean the code needs changing so that it only copies the cells that contain actual data and are not blank at the result of a formula
or
* have the code copy the data as it does, open a new workbook, paste the data, do its thing to remove the "" cells then paste the final data into my required sheet.

Just struggling to figure out how lol

[romperstomper]

The code provided works in your sample workbook once you remove the array formulas (which don't need array-entering anyway, as you know).

[galvinpaddy]

Morning RomperStomper,
From the sample provided by AB33 (with the code im using) if you open it, share the workbook and run the code, the code produces the above error

[romperstomper]

That sample still has array formulas in it - you said you had removed them from your workbook.

[galvinpaddy]

Hi,

yes it does
Post #16 I mentioned that I have been through my workbook and removed all Array formula's.
I could load a fresh sample for you if you like with the array removed?

[romperstomper]

Code works fine for me in that workbook when shared.

As an aside, NEVER use shared workbooks if you value your sanity.

[galvinpaddy]

fair point!!
The work at present is shared as its used in work, 12 lines input completed data into the sheet throughout the day and its then emailed out at the end of each day. The powers that be have declared that a shared workbook is the way forward...... (i value my sanity, they obviously dont lol)

Im incredibly confused, all morning i have been trying various things and the code fails once the workbook is shared, i have literally opened the latest sample, shared it and it works.......
Looks like i need to back track and see exactly what has been changed to allow it to work as a shared book.. lord give me strength!

[romperstomper]

I suspect the powers that be don't know what they're talking about and have no idea of the corruption, data loss and weird behaviour that follows using shared workbooks.

[galvinpaddy]

i agree. we have used shared workbooks for a few years - they maintain that the only reason things go wrong with the sheet is when it is not used properly.
I have another version of my entire workbook that runs pretty much bang on. It has 1 worksheet per line, all reading to one master worksheet, then the control board section that shows everything reads from this sheet. I also have VBA code that refreshes the master hidden sheet every 3 minutes to always show up to date information.
But, i am told its too messy for them to have to look at this sheet reading from that sheet and so on, hence the request for everything to be in one workbook and be shared.

So, it leads me here hoping that whilst for most people the point of 'Shared Workbooks' is a no go, that hopefully this forums can get me to the end even using a nut ache of a workbook and process lol

[romperstomper]

If I were you I'd make sure that it's on record somewhere that you were against using Shared workbooks. Then when it goes wrong you can say 'told you so'.

[galvinpaddy]

have that already lol. Been caught out with something similar before - now if its not in an email as a direct request - it doesn't get done.

[galvinpaddy]

ok - back on track

Pretty sure its ok now as i've been through and had a lok at every formula one one sheet then replaced the others with it i can step through the code with the sheet locked but not shared and its fine. when i run the code with teh sheet locked and shared i now get an error

Application-defined or Object-defined error

but of course cant debug as its shared :D
I'll post the whole code:

Please Login or Register  to view this content.

When the error occurs, i stop the macro, un-share the sheet and it appears as though the code has stopped just before this step:

Please Login or Register  to view this content.

Am i correct in thinking the command to remove duplicates wont work on a shared sheet then??

[romperstomper]

It would appear so from a quick test.

[galvinpaddy]

ok well thats good news then at least you get the same result as me :D

Is the any other way i could remove duplicates without clicking on the button 'remove dups'?

[galvinpaddy]

Been looking at it and I'm guessing that the best way to do it is to get my code to add a tab/worksheet or open a new worksheet, paste my data in there, remove the duplicates and then paste the remaining values back into my sheet and carry on with the rest of the code.
Trouble is I'm drawing blanks around the best way to do it, I think I can get my code to open a new workbook, paste values, remove duplicates, copy and paste back to original sheet and close the extra workbook, but isn't there a way to do this without actually opening a separate sheet? I thought there was some way it could be done in the systems memory? or am I talking carp lol?

[romperstomper]

Add this routine to a normal module:

Please Login or Register  to view this content.

then replace this line:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

[galvinpaddy]

you sir are almost a legend :D
I say almost as i need you new code to do its thing but whatever it does, leave the values as a text string.

Reason - the values before your new addition runs are text (text because a key part of the data it copies is a 6 digit figure, starting with two zeros) but your code omits the leading 0's.
I thought that if i changed 'As Long' to 'As String' that would do it, but it throws up a 'Compile Error: Type mismatch'

[romperstomper]

If the cells are formatted as Text, the numbers shouldn't change.

[galvinpaddy]

mate, someone needs to get you a beer....
Fantastic, really appreciate your help on this one, very much so! thanks alot, you've made my job a great deal easier.

Honestly mate, thanks a lot.

[romperstomper]

Glad to help. (buying me beer can be an expensive thing to do...)

[galvinpaddy]

lol i have to spread the rep before i can add it again!