Hiding a Relative Range of Columns with VBA using R1C1 Format

[Sarvazad]

Hello All,

I have searched the forums and Google and found inconclusive or merely partial implementations to hiding a range of columns (or rows) using R1C1 format in Excel VBA. The goal of this code is to have a set of rows hidden depending on the result read-in by a Case statement. The Case statement works well on its own, so for the sake of clarity and simplification it has been removed from the code below.

Using Excel 2010, the following code portion is from my original, working version that would need to be changed manually for a moving range:

Please Login or Register  to view this content.

To achieve this, I have attempted the following:

Please Login or Register  to view this content.

May I please get some assistance with this issue? I am not sure what is being done incorrectly, but I would also like to know why each method does not work, instead of only a quick fix so I can learn. From what I understand, using ".select" is wasted computation time, particularly if this gets to be a rather large, actively running project.

Thanks you all in advance.

[TMS]

I'm really not entirely sure what you are trying to do. This is one simplistic approach:

Please Login or Register  to view this content.

Regards, TMS

[Sarvazad]

Hi TMS,

Thank you for your quick reply. Unfortunately, your code isn't really relating to what I am trying to accomplish.

First off, this will be a program for other engineers to use. I do not want any pop-ups, as pages will be pulled for data later using batch coding where this may cause a problem. In this code segment however, only that limitation matters in being know outside of what was provided.

What is also not shown, is that depending on a selection from a pull-down, a certain range of columns will be hidden. As shown in my first post, my goal here is to use R1C1 formatting so I may hide these columns, depending on the value of the integer being read-in. Again for simplicity, I have hard-coded the variable, since that part is understood.

If you were to adjust only the part for:

Please Login or Register  to view this content.

versus utilizing R1C1 form for a varying range, with my two different attempts here (these do not work as-is):

Please Login or Register  to view this content.

Ideally I would like to know the difference of methods, WHY something is better and/or works, and what is its consideration with computation time. Thanks!

[TMS]

You don't have to use an InputBox. That was simply a means to input the data ... that is, a start column and a number of columns to hide.

You have the advantage of knowing what your workbook, your worksheet(s), your drop down boxes, and your code looks like. I don't.

And I really don't understand what you mean by R1C1 referencing. It's not a formula in a worksheet.

I have a demonstrated a means for you to specify a start column and a number of columns and then use that to hide the columns. I suggest that you maybe look at it again and see if you can adapt it to pick up the information from your drop down cells.

Regards, TMS

[Sarvazad]

Hi TMS,

Thank you again for your input. I am a newer user of VBA and self-taught, so perhaps I am missing some of the aspects you had mentioned. I apologize if my last post came off as rather gruff.

I have been approaching this as one would matrices and specifying a unit within a matrix. What I mean by R1C1 referencing in this way, is rather than referring to utilizing as a command on its own, the difference of "A1" versus "R1C1" or "N2" versus "R2C14" versus utilizing Cells(2,14).

Ideally I was trying to understand a method within this scope so some numerical methods of non-linear analysis may eventually be utilized in a similar manner. Is there a fundamental problem I am not understanding or aware of with the approaches I was attempting above?

Looking at the section of the code you provided extracted below, there are a few things I am not fully understanding:

Please Login or Register  to view this content.

I do understand the If Else structure, but I may be mistaken, but I don't believe the "Columns" command works on its own like that with Excel 2010, only Excel 2013 and up. Either that, or it is hidden on a sub-section at http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx and I may not have utilized this correctly or fully in the past.

I am also not quite clear why you are using the "CLNg", .resize, or the "IsNumeric" commands. I know the "IsNumeric" command returns the Boolean "True" when it passes, so I could see that as a throttle for the if/else selection. I also see how 'CLNg' converts a value to a long integer; is that needed to have simple integer implementation with an algebraic action, even below 256 for an unsigned value or +- 128 for a signed value?

Thanks in advance.

[TMS]

When you want to use VBA to put a formula in a cell, all the following do the same thing ... assuming that cell C2 is the Active Cell. So, for example, if you have previously selected the cell where you will put the formula, you get what you expect.

And you can apply the formula to multiple cells, regardless of whether you use A1 notation, or R1C1 notation.

Please Login or Register  to view this content.

When you refer to cells or ranges in VBA, you can use Range or Cells, or Columns or Rows, depending on what you are trying to do. Some methods are easier to use than others, sometimes. Ideally, you should not select cells and then refer to the ActiveCell or Selection. Hence the non-selection approaches above are better ... quicker and more efficient.

If you want to manipulate a column in VBA, again, there are different methods. The following lines of code all do the same thing:

Please Login or Register  to view this content.

Just variations on a theme.

Rather than hard code the row and column, you can use variables as shown above. All you need to do is decide how to input those values into the variables. I used InputBoxes. You will use cell values.


Regards, TMS

[TMS]

Another couple of examples:

Please Login or Register  to view this content.

Regards, TMS

[Sarvazad]

Hi TMS,

Thank you for going into depth with your reply. I appreciate it, since I have learned a lot more that way not just with what works, but understanding the computational advantage with regards to "selecting" files. Hopefully it will help others in the future as well. Looking at what you have illustrated, I believe I understand more about formulas and Column references.

Correct me if i am wrong, but it seems my issue is that I wanted to apply too many operations at once. If I want to have an mathematical or logical action done as entry to a formula's value to be inputted to the sheet there won't be an issue. However, when attempting to format the sheet, such as in my case with columns, only an integer can be inputted to the called command. So rather than using:

Please Login or Register  to view this content.

I will have to make a variable for each value to be called, instead of having this operation done as a nest within a single statement. So in actuality, something along these lines should be done, using one of the methods you illustrated:

Please Login or Register  to view this content.

In this case, the columns chosen on the left hand side in the parentheses of ".Columns()" will be bound in the range to the right of the second value of ".Resize()", taking the entire array since no value way called. Then this contained range has its Hidden Boolean adjusted by the statement at the end. Testing this, I was able to make the code work, but I want to make sure I am understanding correctly.

Thank you again for all your help so far!

[TMS]

Yes, I think so The variable in the brackets in ".Columns()" determines the first column to be hidden. The variable in the brackets in ".Resize()" determines how many columns are to be hidden. Note the comma (,) before the variable as the first parameter (rows) is not needed here.

In your example, it looks as though you want to hide 26 columns starting at column 14.

I tend to use Long for variable relating to row or column counts. Probably not essential for columns but it is for rows and I like to be consistent.

Rather than define and set variables, you could actually refer directly to the cell ranges where you have selected the values.

Regards, TMS