Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Removing Bolded Rows with Multiple Headings per worksheet.

Posted on 2014-02-04
12
Medium Priority
?
134 Views
Last Modified: 2014-02-07
With a Workbook.xlsm, which had the code assistance of gowflow, and which may have up to six worksheets, for each one, with multiple common headings per worksheet,  what is the best way to remove the non-bolded data rows, so that all that is left is the specific bolded data rows, within each worksheet, which would then be manually transfered to a Word document?

Cook09
0
Comment
Question by:Cook09
  • 7
  • 5
12 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39833216
Could you post the workbook please
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39833298
I just noticed your title says:
Removing Bolded Rows ....

and then the text of the question says differently:
 what is the best way to remove the non-bolded data rows, ...

So what is it ? Bolded or non Bolded ?

then if we endup with headings and comments and nothing underneath you want to delete the heading as well ? and if the worksheet endup with only the title and few lines shall we delete the whole worksheet ???

Pls be specific by posting a sample.
gowlfow
0
 

Author Comment

by:Cook09
ID: 39833426
Sorry, It should be removing non-bolded rows. In thinking about it, this really only applies to the Late and Missing.  The Posted Late is Late past the 30 minutes, so those are going to bolded.  The Misdelivered, doesn't really take "Time" into cosideration, and there should there should be no Error Items.

 However, all of the data for the Late and Missing needs to be there within the workbook as it is used for storing and auditing.  Clearing the non-bolded, makes it easier to put the bolded ones onto the Word.doc Report.  

It would seem to be easier, just to make another worksheet for just the non-bolded Late & Missing rows.  But, you can decide that.

I added one additional Late and Missing page to provide an example of why this is beneficial. The "Late and Missing new" provides an example that you wanted. Although, if another worksheet is provided, grouped by Destination, the non-bolded rows could be left off, as long as we have the "original data."

Cook
Error-Log-Report-V02.xlsm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 39833676
In late & Missing New
Row 13,14 15 what do you consider this to be ? bold or not bold
Col A is not bold the rest is

what is the criteria to choose none bolded.
Still you did not answer if a section end up with the header and the row before what we do delete the header or keep the header with no data underneath ?

gowflow
0
 

Author Comment

by:Cook09
ID: 39834028
Row 13,14,15 and any row on this spreadsheet that is primarily bolded, should have been all bolded.  I don't know why they weren't.  

The bolding criteria is column H, as per the wording under Late & Missing.  If the value is greater than 24 hours then the entire row (A:I) should be bolded.  Less than 24 hours is not bolded.

If there is no row underneath the header, or just one row that is not bolded, then the header (and row) should be deleted.  It serves no purpose just being by itself.   Single bolded rows should retain their heading.

My wording was reversed in a comment above, if an extra worksheet is deemed to be the best method, then the bolded rows, with their headers should be on that one, and the (original) can be left as is.

Hope this answers most of what you need.

Cook
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39834930
ok here it is
The routine will despite what you said run All the worksheets !!! and remove non bolded rows as you may possibly have like the one posted Late & Missing New, Late & Missing Original whatever. It will skip Error Items sheet.

PLease try it and let me know. Headers have been adjusted to fit when deleted.
gowflow
Error-Log-Report-V03.xlsm
0
 

Author Comment

by:Cook09
ID: 39836123
gowflow -

The removal of the non-bolded rows seems to work as expected.  The original workbook, with all data, even if it's reformatted, needs to be kept for auditing purposes.    However, it needs to be saved as a different workbook, due to the removal of the non-bolded data.  

I inserted something that would allow the user to save it, if they somehow wanted to save it differently than suggested. But if you have a better process, please delete and insert.  

This Save As is also in the orginal reformat code, as the user wants to have the discretion on where the file needs to be saved and/or the name of the file.  Normally, they will save as-is, but they want the capability to change either the name or the location.  So, accomadations have to be made.

Cook
Error-Log-Report-V03.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39836242
I do not have a problem but pls sake good order we need to keep threads as short as possible and within the strict frame of the question. As the question here is about non bolded rows to be deleted, pls assess if this was well delivered in this scope then pls close this question and will be glad to assist in any other related or non related question you may need help with.

remember EE is on global search engines and solutions provided can help lots of people having same issues (I am talking in general of course) reason why it is important to keep threads strictly confined to questions and their immediate contents.

Regards
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39836359
I looked at your code for saving which definitively need reworking especially if what you said about:


The original workbook, with all data, even if it's reformatted, needs to be kept for auditing purposes.

The way you are doing it fix half of the problem but still the user is capable to save the present file after the routine has ended and therefore it will no more stay in its original format !!

gowflow
0
 

Author Closing Comment

by:Cook09
ID: 39837118
It fully meets the requirements of what was requested and now everything rolls up into one easy to copy sequence.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39837149
if your interested in fine-tuning a save pls post a new question and will be glad to assist.
gowflow
0
 

Author Comment

by:Cook09
ID: 39842462
gowflow,
I think I can live with the saving for the moment...it seems to work, most people just accept the default.   There are other items that I'd like to move into that would enhance the overall process, as well as some BI that has been developed over past few months.  The first question will involve e-mailing, but that is more of a Word.doc question, but for Excel it will be about appending the Excel database when the bolded rows are created.  Then I'll have some questions about Pivots, but will try to keep each question to individual pieces, that will eventually create a whole.

Excel Question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28359380.html

Word / Outlook Question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28359369.html

Cook
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question