Posts by mdenino

BadBunny, I tried your code and found that after changing the cell format, the line .Value = .Value did, indeed, convert the number-as-text to an actual number.

KjBox, a loopless solution would be great and may exist, but I did not find that numbers-as-text got changed into actual numbers using your solution.

Edit: Well, duh. Of course it can be done. Just replicate BadBunny's solution by adding another line:

objWksh.Cells(1, 1).CurrentRegion.Columns(16).value = objWksh.Cells(1, 1).CurrentRegion.Columns(16).value

(Can surely be cleaned up further using a With statement.)

Quote

*By 'adding a column of data', I meant that I will add data to between 5-8 cells in column F, leaving the other cells empty.

It sounds like you are selecting column F and inserting a new column, which shifts column F and beyond to the right. If so, I believe what you'd like is easily accomplished with a named range.

1. Select the range that contains your data (Originally C5:BE44)
2. In the name box above the column headers, type the name you'd like to give to the "flexible" range.
   Alternately, name it using the Name Manager, which can be accessed via Formulas > Define Name or Formulas > Name Manager > New
   (Via keyboard: Alt-M-M-D or Alt-M-N-N ... or Alt-I-N-D-N, which hearkens back to pre-ribbon days!)
3. Construct (or edit) your hyperlink in cell C51 as before. You will notice that your newly named range will be shown as an option.

Now, anytime you insert a new column anywhere from column D to BE, your named range will grow by one column. (If you insert a column before C, your named range will instead shift to the right.) The same holds true for inserting a partial column, as long as it includes at a minimum rows 5 thru 44.

Hope I have understood your need correctly, and this solution works for you. Let us know.

Does it work as intended once you add PtrSafe to the declarations?

Please provide some more information.

1. What method did you originally use to create the hyperlink? A "HYPERLINK" formula in a cell? The Link button on the Insert ribbon? VBA? Something else?
2. Please explain "I added a column of data to D4." Do you really mean that you added a column of data to a single cell?

rory (or groot, if you prefer), thank you! That is exactly the issue.

I have confirmed this by going to the 'cs' sheet and removing named ranges one by one until I found the problematic one. Then I added them all back in and removed all conditional formats that did not reference that named range. In the end, the combination of one named range and two conditional formats was sufficient to produce the problem. And when I modified the conditional format formulas, the problem abated.

I was incorrect to state up front that all of the same conditional formatting on the slow sheets was present on the normal sheet. The sheet that was working well did not have the previous-row comparison.

The ::facepalm:: thing is that I had all the information I should have needed to discover this much sooner, if I'd just taken a more careful analytical approach. I knew that the four problematic sheets were created to handle a one-to-many relationship on one of the columns, and I was the one that developed the CF to make rows that had a common value in one of the columns appear grouped. What I don't know is why I only began experiencing the slowdown recently, instead of immediately when I added the problematic CF.

In any event, you have added to my knowledge with your observation that INDEX(namedrange,0) will return every cell in the range. I had never made that connection before!

Thanks again!

Now, how do I mark this thread resolved?

Objection: Assumes facts not in evidence.

Treating the root cause is how problems get fixed. Anything short of that is known as treating a symptom.

KjBox, nowhere have you addressed, or sought to address, why the behavior does not significantly affect the sheet with the most named ranges and conditional formats, which is what my question is about.

Moreover, if I leave all conditional formats in place and instead delete the named ranges, then the problem is alleviated as well. That demonstrates a correlation, not a cause.

Finally, I previously stated that some sheets contain borders throughout while others do not, and explained that the ones that do not contain them perform the same as those that do. That demonstrates the lack of a correlation.

I truly do appreciate your interest in helping. But unfortunately it has not been successful.

I don't want to argue. I appreciate your efforts. It just hasn't worked out.

Thanks, anyway, for your efforts. I know you took some time with it.

May I suggest that if you don't have time to attempt to help me answer my question, then you could've already saved yourself a lot of time spent communicating about other things?

I do not see where you have identified a "conflict between conditional formatting and excel's functionality" that exists on the slow sheets, but not on the faster one. It's as though you're trying to say the problem is the cause of the problem. Except, of course, that conditional formatting has not been fingered as a root cause.

Again, I appreciate your engagement, but you are seeking to solve a problem I am not asking about. It's right in the title of my post:

Unexplained performance hit on some worksheets but not others

Any response that is not geared toward identifying the underlying reasons why the performance hit affects some worksheets but not others is not pertinent to the question I asked. I am seeking to identify a root cause, not employ a corrective action. I don't know how I can be any clearer about that.

I feel I should also point out that this statement is not accurate:

Quote

For a start, setting Calculation to Manual, affects only formulas in cells, it has no effect on the formulas used in conditional formatting.

To prove this to yourself:

1. Conditionally format cell A1 of a blank sheet to fill with a color when the equation, =B1 > 0 is true.
2. Change calculation to Manual
3. Enter a value in cell B1 (and note that cell A1 does not change color)
4. Hit F9 (and note that A1 changes color)

See?

Consider:

Sub Text2Num()
Dim cel As Range
    For Each cel In Selection
        cel.NumberFormat = "General"
        If IsNumeric(cel) Then cel = CDbl(cel)
    Next
End Sub

KjBox, I appreciate the time you spent examining the workbook and composing your response, and I understand what you are saying. However, I think you overlooked the core matter I am seeking to understand.

The question is why the four considerably simpler worksheets perform slowly, while the more complicated one does not.

The first worksheet, named "c", which contains far more named ranges and conditional formats, performs acceptably. The other four worksheets are considerably simpler, but perform slowly. Even if you delete the first four worksheets I believe you will find, as I have, that the fifth one still performs poorly.

Moreover, I don't believe your explanation is correct. If it were, I should be able to avoid the slowdown effect by setting the calculation mode to manual insetad of automatic. But that is not what occurs. (Note that I have avoided the use of volatile functions in the conditional formats.)

Again, I appreicate the interest & input, but I think something else is going on here.

If your experience of this workbook's performance differs from what I have described, I would be interested in that information.

Thanks.

OK, here at long last is the stripped-down workbook.

I've reduced it to only 5 worksheets. The first is the one that's functioning well. The other 4 are very slow. (Sample size: 2 computers. So, um, yeah.) There's no data in the workbook.

The unaltered workbook is a template, and has never held much data -- only that which was useful to demonstrate features during development.

The "good" sheet is a core sheet that has been along for the ride through multiple development iterations. The four troublesome sheets are relatively recent additions.

You may notice that some sheets have border formatting in every cell, while others have none. I was experimenting with getting rid of it but found no advantage to doing so. It is not contributing to the used range.

Thanks again for having a look.

My guess is there is a rather complicated series of formulas that would do this for you. However, it would be much more easily accomplished with a data model.

Is Power Pivot an option for you?

In E3: =SUMPRODUCT(--(YEAR($A$3:$A$7)=$D3),$B$3:$B$7)

Copy to E4.

You're welcome. I'm glad you got what you needed.

OK, if I understand correctly, you could use this single formula:

=IFERROR(AND(MATCH($A2,[Book1]Sheet1!$A:$A,0)>0,$B2=INDEX([Book1]Sheet1!$B:$B,MATCH($A2,[Book1]Sheet1!$A:$A,0)),$C2=INDEX([Book1]Sheet1!$C:$C,MATCH($A2,[Book1]Sheet1!$A:$A,0))),FALSE)

The output is TRUE if all three fields on the first sheet match the entries on a single row of the other worksheet, and FALSE if they do not.

If you prefer, when a match is found you could output the row where the match occurs instead of the word TRUE:

=IFERROR(IF(AND(MATCH($A2,[Book1]Sheet1!$A:$A,0)>0,$B2=INDEX([Book1]Sheet1!$B:$B,MATCH($A2,[Book1]Sheet1!$A:$A,0)),$C2=INDEX([Book1]Sheet1!$C:$C,MATCH($A2,[Book1]Sheet1!$A:$A,0))),"Row "&MATCH($A2,[Book1]Sheet1!$A:$A,0),FALSE),FALSE)

Hope that gets you what you need.

ShoaibRajput1, I think we are having a communication challenge. It looks like your solution is already working. I am not completely clear about what you still need.

If you'd like a VBA solution, you could record a macro in which you add the CONCATENATE and the VLOOKUP columns.

Thank you for your continued engagement. Yes - defined names on slow sheets consist of two rows and anywhere from 8 to 22 columns.

On the most encumbered sheet, which performs just fine, there are two defined name rows and 52 defined name columns.

More info:

• Starting from the file I just re-saved as xlsb (see comment #6 above), I just deleted all of the "OK" sheets and cleared out all the defined names that pertained to those sheets.
• I confirmed that on the "bad" sheets, the used range is the last header cell in row 2 (verified by ctrl-end, as well as by ?Application.ActiveSheet.UsedRange.Address in the VBA console's Immediate window.

The problem persists.

I am leaning toward recreating the workbook from scratch. But I sure would like to know what's causing it.

Before I turned the original xlsb file into an xlsx file, I encountered Excel's Ghost Break problem for the first time. This leads me to suspect that my workbook may just be cluttered with undetected artifacts from previous incarnations.

That said, with everything I have stripped out of it, it is only 197 KB. So.... ya know ..... ¯\_(ツ)_/¯

I have inconsistently been able to edit my posts. Sometimes I can do it without issue, other times I am informed I'm not authorized.

Is this a time-limited feature, or is something else going on?

(Also, if there was a more appropriate place to ask or look for the answer to this question, please point me there!)

Thanks!

Quote from royUK

Have you tried saving as xlsb?

It began as xlsb, but also had code behind it. I saved it as xlsx as a shortcut way to strip out the code.

It was a good thought. I resaved as xlsb without the code. Unfortunately, there was no noticeable difference in performance.