?
Solved

Cell RowHeight and ColumnWidth BOTH register zero - how ?

Posted on 2013-11-19
10
Medium Priority
?
310 Views
Last Modified: 2013-11-23
I am working on a VBA solution where it checks the value of a hidden range of cells to see if that column has been flagged for processing. As I say the column is shown (say a column width of 18) but the row is hidden.

My issue is that on one test the 1st cell in the hidden range the RowHeight returns 0 AND the ColumnWidth returns 0. The worksheet does however show cells. My question is : how is this possible ? Normally these two measurements cannot both be zero or else there would be no cells shown (i.e. none of the columns or rows).

Can any expert suggest how this can happen ?

NOTE: I have made sure the worksheet is not protected
0
Comment
Question by:AL_XResearch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 39661266
Can you post the file so we can see the issue?
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39661644
Etech0; unfortunately I cannot - I should have mentioned that initially.

I have worked many many  times in IT support so I know how limiting and irritating that it is but a) it is part of a multiple workbook solution that involves two books working together b) is extremely complicated and I would need to train you on that area of the system for you to understand what I am trying to do and to know which function to look in c) it is for a customer and so confidential d)

I have not, so far, been able to reproduce independently. With all that said it does happen consistently in the system.

I have tested, debuged and tracked down the issue to this root issue.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39662141
Hi,

Why not just copy the single worksheet with the issue to a new workbook & copy'n'paste the data shown "As Values" so there are no links to any other worksheets/workbooks?

If the data is sensitive, simply replace each cell with a value with an "X" or something similar.

BFN,

fp.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Author Comment

by:AL_XResearch
ID: 39662177
Ok, I think I know what has been going on and it is in part my fault. I was unknowingly passing a range where both the row and the column that I was measureing were hidden (i.e. rowHeight = 0 and ColumnWidth = 0) - so that answers that.

The other part that is a little confusing is as follows and thus changes my question:

When you have a column with width of 9 and you hide the column in VBA the ColumnWidth reads as 0 but if you unhide the column using the excel interface the column width returns to 9. This behaviour causes a problem for my function that temporarily gives a hidden column (one with ColumnWidth = 0) a minimum width of 0.75 to allow searching on it's value and then restores it's previous column width (in this case 0) when finished.


How can I tell what columnwidth a cell was set to BEFORE it was hidden when it's columnWidth is set to 0 once hidden ? Excel appears to be able to do this but not via VBA ? Is this just one of Excel's oddities and limitations ?
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 2000 total points
ID: 39662234
Is this just one of Excel's oddities and limitations ?

Yes.

You could always store the original value in an in-cell Comment of one of the cells in that column prior to changing it, & then read the contents of the Comment if ever you need to query the value before reinstatement to visible (then remove the Comment).

Alternatively, if you only ever have one column hidden at once, you could simply have a Global (Public) variable holding the previous width value.

If you have multiple columns hidden simultaneously, then a Public Array variable could be used, or you could store the width in an unused row somewhere in the column(s).
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39662254
fanpages: I am very sorry to read your post, I had hoped someone would come back and tell me I had missed something all these years. Your suggestion is just what I had been writing before I read your post (a version thereof anyhow). I am storing the column width for the key columns in a hidden row when the workbook is opened. I can the apply the correct width whenever I show those cells again.

Does anyone else have another suggestion / insight ?
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39662267
Is there really no property that stores the 'unhidden colwidth' ? That is a huge Excel omission.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39663204
You might like to try this workaround

Set cr = Range("D:D").EntireColumn
If cr.ColumnWidth = 0 Then
    cr.Hidden = False
        Debug.Print cr.ColumnWidth
    cr.Hidden = True
Else
    Debug.Print cr.ColumnWidth
End If
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39664037
You may wish to enclose ssaqibh's "workaround" with the following two statements:

Application.ScreenUpdating = False

(ssaqibh's code)

Application.ScreenUpdating = True


This will ensure the person using the workbook does not see the column become visible, & then hidden again.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39665400
ssaqibh & fanpages: Thanks for your responses. I have already used both these concepts in the solution I discussed in my previous post. One of the main issues, apart from the incorrect ColumnWidth of zero as above, is that the range being used has a mixture of columns some hidden and some unhidden.

The final solution I came up with was to use 2 boolean arrays that store the hidden property of each cell's row and column, one array for the row index and one for the column. At the end of my function the code reads the array and re-applies the 'hidden' status.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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