If a1 is blank do nothing, if filled, return Row

Hi born2dive00

Here's a vba method to do what I think you are asking for.

see attached file

zzzeddy
•Environmental Emergencies Planner

THAT IS SICKLY Awesome!!! THANKS!!!

I do however have a question, How do i edit the macros to add additional lists? I will have about 20 sheets of data I will need to compound like this.

Also with the merged sheets, I need to put it in tables so that I can sort each column. When I tried to insert table, it wont let me do so, why? Please let me know Thanks!

Originally Posted by zzzeddy

Hi born2dive00

Here's a vba method to do what I think you are asking for.

see attached file

zzzeddy
•Environmental Emergencies Planner

Dear ZZZedy,

I liked your way of code explanation of each step of code.
keep it up.

Hello Dave, when I put it in and excuted the command the first time,
It propagated as I wanted, and I was able to install a sort table which was great,

however the blank spaces that were in the tables, also appeared in the list.
2. When I tried to execute it the second time from the developer, it came back with a run time error every time.

I dont know what I am doing wrong please let me know.

This is the actual database that I am trying to get the table to sort out.

Originally Posted by davesexcel

it replaces your previous code

Hello Dave We are playing post tag with eachother, you post dont come thru until after I posted a response, LOL

I got the New Book THANK YOU!!!!!!!!!!!!!!!!! THAT IS EXACTLY WHAT I NEEDED!!!!!!!!!!!! Thank you thank you thank you, the sort is fast and efficient, removes out the blanks and has a sort table!!!!! I could give you a huge hug!!!

Something I can work with, like I said VBA is NOT one of my strong suit's


One last question, If I add another table (this one I dont want to sort into the master list) with the existing code will it sort it in or no?

Please let me know

Originally Posted by davesexcel

Yes. sometimes we will get an error when deleting rows because it is a table.
I have edited the code to convert the table to a range, then deleting the rows, get the data, and at the
end convert the range back to a table.

See attached as well, the button has the new code.

Please Login or Register  to view this content.

Hello Dave,

One small last favor,
I added another sheet called FEMA #

This sheet has been sorted in with the others at the end, This is a reference list to beused to propagate the FEMA numbers in the master list, Is there anyway we can prevent it from being sorted in with the larger master list?

I have attached the new book, on the masters list you will see where it is added to the list, (which I dont want)

Originally Posted by davesexcel

Just to make sure, have you looked at my last post?

Sorry about that Dave, I will use the one you sent me It is Great, I will just transfer the sheets over to it once I know how to keep them from sorting into the master list.

Here is YOUR BEAUTIFUL BOOK with one of the new sheets added it is called FEMA#

I want to add additional sheets but not have them sorted (these will be reference sheets only to propagate more data later)

Could you please help me one last time, to keep additional sheets from being added?

Thank you!!!!

Originally Posted by davesexcel

You keep changing the sample workbook. Your new sample does not have a table on the first sheet and you have hidden worksheets as well.

Try this with your latest sample.

Please Login or Register  to view this content.

Hi

Glad see some more replies.
In my attached file, I've added code to process all data sheets except those with sheetnames beginning with FEMA
And I took the liberty of adding a column [A] on the [Master Data list] sheet to identify the source sheet for the merged records. This allows you to resort the records again after you have say, sorted on another column.

My code could be made to run faster, but I wanted to show the steps.

zzzeddy
Excel Drain Surgeon

Hi

Another way of processing the worksheets could be to skip all data sheets that are a particular colour, for example
If ActiveSheet.Tab.Color <> vbGreen Then 'don't process the sheet if it's tab color is green
etc etc etc

zeddy
Excel Organic Hoist Specialist

Originally Posted by sathishsusa

Dear ZZZedy,

I liked your way of code explanation of each step of code.
keep it up.

Thank you - I appreciate that

zzzeddy
Excel Internal Solar Sales Consultant

Hi born2dive

..just checking to see if you've looked at my post#17 above

..I see you have asked another question about this in another thread, but I haven't worked out how to reference other threads yet.

zeddy
Excel Luxury Sheets Division

Hello ZZZeddy

Yes I got the last book, Love what you did and SUPER FAST!!!! The book does 97% of what I need it to do, but I could use your help as I cant seem to find the correct code to change some things

I have done a couple of tweeks 1 being I combined the 2 buttons to one, I spliced the code so that when it merges it first clears then sorts, If you forgot to clear it, the data base doubled, trippled.....

Where I can use your help is the following.

1. On the master list, Can we remove the data in Column A as I need this blank. I have added the material types on the actual sheets.

2. Now I added another sheet called Personal Data, In the purple tab. This is where I can add at a later time, additional materials... There are some 500 blank spaces that I can add material,

However when I sort it in the Master Data list, the blank lines from the Personal Data list and the Sheet4 for some reason are appearing .Starting at row 2864 and below.

The other reason I want to eliminate the data in Column A on the master Data list, is for some in column A at row 2863 is saying Sheet 4, while the correct information from the Personal Data base in purple is saying it is from the personal database.

3. When I add additional reference sheets like sheet4,
Is there any way that we can creat a pop up that asks me, "You have created a new sheet, do you want this information on this sheet to be sorted into the master list?" Yes (button) No button
If i click No, it will not sort it into the master list, and add this to the skip code (like the FEMA# skip). This will allow me to quickly differentiate between the Sort list data, and the Reference (hidden data)

Please let me know. I am attaching the book that I tweaked and could use your help with.

Hi born2dive

In your last attached file in post#21, look at your heading row 3 on sheet [Master Data list], columns [M] to [BA]
..what are they for???
..I removed them in the version v3a file attached.

Instead of removing the [Step 1] button, you could just call the Step1 routine at the start of the Step2 routine.
Putting..
step1
..as the first line of code in the Step2 routine achieves what you wanted i.e. always clearing before merging.

By keeping the [Step 1] button, you can now halve the file-size by clearing the data before saving your file.
TIP:
if you want to reduce the filesize even more, just save your Excel files as .xlsb filetype (my favourite).
Smaller-sized files are faster to load and reduce network traffic. Just saying.

Can we remove the data in Column A as I need this blank

Yes, this was a simple job: just commented-out those lines of code.

To simplify which datasheets get processed, i.e. merged into the [Master Data list], you could have a simple rule, like, "if the sheet tabname starts with a space, don't process this sheet"

So, I changed the code to implement this example rule. Any sheet you don't want included in the merg, just add a space prefix to the start of the sheet tabname e.g rename [FEMA #] as [ FEMA #], and rename [Sheet4] as [ Sheet4] etc etc etc.
It is a lot simpler than having a pop-up and writing more code.
Unless you forget the rule of course. In which case, add a note on the main sheet.

You could use a different simple rule, for example, "don't merge any sheets if the sheet tabname colour is, say, black."

Regarding the 'blank lines from the Personal Data list'.
This is due to the method used of finding the last row. One common method is the [End] [Up]method. If you start at the bottom of a column, and press keys [End] [Up], it will 'stop' in the first non-blank cell it encounters. However, when you are using Tables, e.g. on your sheet [Personal Database], if you scroll-down and put the cellpointer in say, cell [B510], when you press [End] [Up] it will stop in cell [B500]. Although this is an 'empty' cell, it is actually part of your data Table (it is a 'blank' data-record). It is part of your 'used-range'

So I have changed the code to use a different method of finding the last row:

Please Login or Register  to view this content.

I hope the attached v3a updated file helps with what you need.
If it does, then this help would be valuable. If not, then this help is invaluable.

zeddy
Excel Coding Team Rehabilitation Volunteer Therapist

Hello Zeddy

I just took a look at the V3a The master list column A is clear Great!

I like the space( FEMA) ( Sheet4) rule! However since I will be adding 20 to 30 more reference sheets, Is there a way we can reverse the rule so if the sheet is normal do normal, and use the space rule ie. ( GS Absolutes), ( GS Concretes) for the ones we want to sort into the Master List?

There seems to be 3 new problems with this version.
1 and 2. On the master list when I clear the data and then hit update, It only sorts the absolute list and returns only 32 results (problem 1) ,
then it skips the concretes list (problem 2),
and goes to the essential oil list and returns 30 results (problem 1 again)
then it skips Extracts, Fixed Oils and Carriers, Oleoresin, Terpenless (problem2)
and goes to the Resin balsam and bot and returns everything (which is fine) then it goes to the personal Data Base and returns everything (which is fine)

I looked at the problem 2 thinking it was due to the names having a space before the word i.e. ( GS Terpenless) but when I checked them they did not have a space in them) So I dont know what is wrong.

Problem 3
When I added a sheet with a space, it did not appear as desired! (Which is ok, Please see first comment)
However when I added a sheet without the space and entered 11 rows of test garbage data, and then sorted it, it appeared at cell 502 to 513 right in the middle of the Resin Balsam list and not at the end.


Can you please help me to fix these 3 problems with this.

Also I need to keep this in XLSM because most users in the world (like me) are using older excel (I use 2007) and XLSB is not recognized.

Hey ZZZEDDY

Please NO! based on tab colors! these are just for my visual, and will be turned to no color later!

I kind of get what you are meaning with the cells and the pointer i think... but can you be more specific, Right now it looks like some sheets are not being sorted while others only partly, and others fully.

Is this something to do with why the absolute page only shows 32 sorted entries?????

please let me know

@ZZZEDDY

Got it working, I had to edit the code ever so slightly, but I got the problems we spoke about fixed. EXCEPT 1 I could really use your help on.

I am attaching the workbook that you helped on and renamed it, The one last thing I need is with the space name rule i.e. ( Fema#) currently inorder for Excel to ignore the sheet we need a space. However as I said before when I add a new sheet (which naturally does not have the space) it counts the sheet towards the master list.

I need to reverse this rule so that (FEMA#)= not added on the list, and ( GS Absolutes), ( GS Conretes)....= added to the list Can we fix this small error?

To Summarize adding a space in front of the name on the tab for the Absolute, concretes, essential oil, extracts.... causes it to sort in the master data list
by not adding the space (as when adding a new sheet, it does not add it to the master data sort)

I think I found the line of code that addresses it but when I tried to edit it, it would not work.

Please Login or Register  to view this content.

Please let me know.

Hi born2dive

OOps! Ok, the error occurred because the code that calculated the zLastRow on Sheet(1) got commented out when we got rid of the column [A] entries. I put the zLastRow calc back.

I also adjusted the routine to process sheets with tabnames that start with a 'space'.
You should be able to see the change that was needed.

Updated file attached (with sheet tabnames adjusted with spaces etc etc etc).

zeddy
Excel Pickled Banana Historian

HEY HOOOOO WAY TO GOOOOO THANK YOU ZZZEDDY!!!!!!!!!!!!!!!!!!!!!!!!!



PERFECT!!!!!!!!!!!!!!!!!!!!!!!!!!!


THANK YOU SO SO MUCH!!!!!!! I COULD GIVE YOU A GREAT BIG HUG!!!!!!!!!!!! CHEERS MY FRIEND!!!!!!!!!!!

now I can start doing some very serious work on this data base! namely adding some 10,000 additional flavorings and some 20 to 50 sheets of reference data!!!


Thanks again!!!!!!!!!!!

Solved!!!!!!!!!!!!!