Hide set of columns on selection - a tough one ques for me

[Ashfaque]

Hi,

The following is complicated for me.

I have an civil engineering cost estimation sheet (a sample). I have set of frames for rooms calculation. Therefore, there are specific calculations in each set of columns.

In my excel sheet there are 20 frames. Each frame contains few column data. For example:

K-S This set contains 9 columns (K, L, M, N, O, P, Q, R & S)
T-Z This set contains 7 columns (T,U,V,W,X,Y & Z)
AB-AJ This set contains 9 columns (AB, AC, AD, AE, AF, AG, AH, AI & AJ)
And so on....(please see the attached sheet.

Since the number of set of columns is 20 or sometimes more than 20 which contains many columns, the sheet become so big that it hangs sometime and I can not see.

What I am trying to do is entering a number into B4 cell. Once I input the value in B4 which should be between 1 to 20, the set of columns must hide.

Let us suppose if I input value 2 then first set of column and second set of column should only be displayed only and rest set of columns should hide

If I input value 5 then first five columns column should only be displayed only and rest set of columns should hide.

Hope fully I have explained.....I tried very few line of code and consfused in.

Please have a look at the excel sheet and extend your help.

Thanks,
Ashfaque

 

[Gasman]

I would probably approach it along these lines.

Create a table where you have
1 K:S
2 T:Z

and so on. I'd start it in Row 1 for ease of walking through the range values.

Then walk through this table starting from 1 more than the number in B4 and use the value in the second column as the range to hide.?

This bit of code is doing that for columns

Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer, iShow As Integer, i As Integer
    iShow = Range("B4").Value
    With ActiveSheet
        LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
    End With
    For i = iShow + 1 To LastCol
        Columns(i).EntireColumn.Hidden = True
    Next
End Sub

However you need a reliable way to get the last column used for that method. Specifying the ranges would be easier, also quicker.

You could then use code along the lines of

Range("K:S").EntireColumn.Hidden = True

Hope that gets you started.

 

[Ashfaque]

Thanks Gasman,

But it is not clear with me if the table you want me to put the values wherein should be vertical or horzontal.

Sorry may be I didnt get your properly

Is the way you expecting which I did in pic....please...see..

 

[Gasman]

Yes, exactly that, however I would put all this on a separate sheet and even hide it as well, but that is up to you.

Then find out how many rows you are processing.

I use this to find out how many rows to process, you must use it on a column where ALL the cells have content. Here it is column B. In your case the columns should be the same, else this is going to fail.

    lLastRow = Range("B" & Rows.Count).End(xlUp).Row

Then walk through that range, starting at you starting number in that cell getting the range in the next column and then hide that as it is written.

This way if you add/alter new sets, just add/amend them in that table. Not really an Excel table, just a table of data for you.

However I am thinking of hiding the 'sets' of data not just columns, as otherwise you could hide columns that belong to a set?

The code I supplied previously as an example will do that now.?

Edit: Sorry, where I say set, you say frame.
HTH

 

[Ashfaque]

Hi,

OK, as per the code, it is displaying permanently till column AA which I desired.

Now the requirement is; After column AA I want my ALL those columns to be always visible which have calculations.

And secondly, ALL set of frames should be hide only based on Cell value B4.

Means If the value in cell B4 is suppose 12 then after running code, it should hide all REST OF THE FRAME COLUMNS after 12 START FROM COLUMN AA RIGHT ONWARDS.

And finally, it should take action quickly. I mean for next loop is taking long time to hide columns. It must be immediate at one time same like Unhide code.

Please see the attached PIC and updated Excel sheet for further reference and understanding.

Hopefully I clarified the requirement.

Thanks,

 

[Gasman]

You do not hide the columns one at a time, you hide a range one at a time. You do not have that many ranges, so cannot be slow.?

In one sentence you say you want all column after AA to be shown.
In another you say hide them from column AA right onwards?

From the list only put in those you want to hide. You are complicating it by mixing those you want to see all the time.

 

[Ashfaque]

Sir,

In whole Excel sheet, we have 20 sets of column.

Each set has 2 parts in it. First part contains Frame and second part contain calculations. (Both parts have multiple columns)

In one sentence you say you want all column after AA to be shown

Sir here ALL columns means those which have calculations. This is what I explained in PIC

In another you say hide them from column AA right onwards?

This I mean to say whatever set of frames after AA column, should be displayed based on value inserted in cell B4.

Let us suppose I insert value 3 in B4 then it should display take action only after column AA which means 2nd set from right side onwards (Frame2 and Its part of calculation, Frame3 and its part of caculations and Frame4 and its part of calculations) to be displayed.

And at the rest of the Frames from Frame5 should be hidden but not thier calculations.

Because Calculation's set MUST BE VISIBLE all the tiime.

This is what I explained in PIC.

Hope it clear now.

Thanks,

 

[Gasman]

So I will repeat also

The way i would approach this is

Create the table as I mentioned. Put the column ranges in order for each row. So if you had 1 in B1 only AB:AK would be displayed. You would then do that by starting from row 2 and hide all the ranges in the next column until the end of data in the rows.

As the calculation ranges are not in the table, they will not be touched.

Also if you use Application.ScreenUpdating =False at the start and Application.ScreenUpdating = True at the end, you will not see any screen flicker.

 

[Ashfaque]

Sir,

I created the tbl as you said and placed only those column range that will be displayed only based on the value I will put in B4 cell.

But it is fully hiding from AB till MK INCLUDING all calculations columns where as If i put 2 in B$ cell and click to btn, it should display only

Till AA i need always display which is done. The effect should take from column AB onwards only. To Identify the Frame and It calculations I have colored them. Light Orange as Frame and Gray as Calculations.

Please check the attached

 

[Gasman]

That is because you are running this code

Sub LastColumnInOneRow()
Application.ScreenUpdating = False
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer, iShow As Integer, i As Integer
    'iShow = Range("B3").Value
    iShow = 27
    With ActiveSheet
        LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
    End With
    [COLOR="Red"]For i = iShow + 1 To LastCol

        Columns(i).EntireColumn.Hidden = True

    Next[/COLOR]
]Application.ScreenUpdating = True

End Sub

I was hoping I would not have to write it for you, as you will learn nothing that way, but this is just dragging on.

Attached is the amended file that should now do what you want.
Please learn from this.

FWIW to any others the code is

Sub UnhideColumns()
Range("AB:MK").EntireColumn.Hidden = False
End Sub
Sub HideColumns()
Dim iB4 As Integer, iLastrow As Integer
Dim strRange As String
Dim rng As Range

' Hide screen updating
Application.ScreenUpdating = False

'Unhide all columns to start afresh
UnhideColumns

' Get start and end rows
iB4 = Range("B4").Value + 1
iLastrow = Range("D" & Rows.Count).End(xlUp).Row

' Now hide relevant columns
For Each rng In Range("D" & iB4 & ":D" & iLastrow)
    Range(rng.Value).EntireColumn.Hidden = True
Next

' Reveal screen updating
Application.ScreenUpdating = True

Range("B4").Select
MsgBox "Columns " & Range("D" & iB4) & " to " & Range("D" & iLastrow) & " now hidden!"

End Sub

The UnhideColumns could be enhanced in case the columns increase in a similar way to how I find the last row to use in column D. You have the code for last column already.

 

[Ashfaque]

Thanks Gasman,

Really appreciate.

It is working as per requirement...

Excellent....