PDA

View Full Version : Lost Data formatting



jwise
10-27-2016, 06:09 PM
Hello,

I have a workbook with 40+ worksheets of derived data. This data is exhaustive, i.e. only a subset of the 110+ rows in these 40+ worksheets is important. I wrote code to dynamically delete the unneeded rows in each of these worksheets, but the resulting worksheets lose all their formatting; this includes landscape orientation instead of portrait and all headers and footers. "Range" delete is used to delete the unnecessary rows-- the worksheet is not altered any other way.

Is there a way to preserve the formatting? If I copy this worksheet to another workbook will the formatting be preserved?

This is 2010 Excel.


Thanks.

Paul_Hossler
10-27-2016, 06:20 PM
What does you code look like?

I hope you had a backup of your original WB

Actually, if you could post an extract with just 2 or 3 sheets and the macro that'd be helpful

jwise
10-27-2016, 06:53 PM
Thanks for your reply.

The code is quite complicated, actually not that complicated but indirect. Since I don't decide which of these rows to include, I had to devise a way for the management to easily specify which rows to include. All of the worksheets have exactly the same rows, i.e. the numbers are different but row 28 (for example) has the same kind of information on any given worksheet. So I created another worksheet that has a list of these data types, and I send the manager this worksheet. He puts an "S" in a third column by each expense he wants to analyze. I put this worksheet back in the workbook and process it. I use it to build a "range" of rows to delete (based on whether there is an "S" in the row). The resulting string looks like "A4:K4, A5:K5, A6:K6" This example would cause rows 4, 5, and 6 to be deleted. The reason I do this convoluted approach is most people take a look at one of these reports and then decide to add or delete some other row. My flexibility is improved. The delete looks like this:


Set delRng = wsInfo.Range(strRng)
delRng.Delete

delRng is a Range, wsInfo is a worksheet, and before this code is executed I call a sub which builds the string strRng.

Aussiebear
10-28-2016, 02:30 AM
I fail to see how this could be construed as a constructive thread. Firstly you ask for assistance, then when kindly prompted to supply either the code in full or partial, or a sample workbook..... you simply skirt around the issue. If you want help, then try being HELPFUL. After 289 posts you should know better.

jwise
10-28-2016, 09:03 AM
I apologize for my inadequate information. Since the workbook has in excess of 10,000 line of code, I thought supplying this might cloud my issue even more. Also, the code that calculates the range is quite abstract, and whether it works or not is irrelevant to the issue. It must work because the worksheets have the correct resulting rows. I actually thought I had discovered an Excel bug or a different strategy could be used to delete the non-essential rows. I had no intention of wasting anyone's time and thought that others might have formatted worksheets and need to delete some or most of those rows while preserving the formatting.

I attempted to construct a more succinct example which had manageable code but was unable to do so. This points to the problem as being my bug. It won't be the first time I goofed. Since I can't reproduce the problem at its base level, I'll withdraw it and mark the thread as solved. I just wish I had a way to find my issue since single stepping through the debugger doesn't.

jwise
10-28-2016, 04:33 PM
I have since found another issue that is probably underneath all this. I was not careful enough about "ActiveWorksheet" versus explicit specification of the sheet. I may survive after all.

Aussiebear
10-28-2016, 04:34 PM
You don't have a solution yet want to mark it as Solved. Why?

jwise
10-31-2016, 08:47 AM
Thank you for the replies Aussiebear and saidee24.

Aussiebear: I marked the thread as solved because you said "... After 289 posts, you should know better." I would have preferred it to be marked "withdrawn" but I couldn't find a way.

saidee24: I am unable to understand your suggestion fully. I have rebooted the PC and the problem remains. The file is not corrupted because I keep separate backup files and never seem to crash one when I do. (I haven't had a file problem since I transitioned from Excel 2003 to 2010.)

My symptoms did change once I found my inconsistency in the subroutine call that applies the formatting. This subroutine was built using the macro recorder. Before my formats were being applied to the wrong worksheet in a preliminary macro. Thus when I deleted the unnecessary rows, the formatting was gone because it was never there. My test workbook that I eventually prepared preserved the formatting, thus I started looking at the predecessor code where the formatting was done. Hopefully, this will clarify:

Routine 1: Build a set of 41 worksheets that have consolidated information, i.e. the data is merged from multiple preexisting worksheets for each of these 41 worksheets.

Routine 2: Delete rows from this derived set of worksheets so the end user sees only the management determined significant rows. The same rows are deleted from all 41 worksheets.

The aside from this is that these deleted rows will probably change once the management sees the output, i.e. the delete function has to be flexible because the specification will change.

I'm still debugging this routine; if I can't fix it, I'll open a new thread.