Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-06-2019, 11:49 AM   #1
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Hide set of columns on selection - a tough one ques for me

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
Attached Files
File Type: zip MD Frame 24.zip (551.6 KB, 14 views)

Ashfaque is offline   Reply With Quote
Old 02-06-2019, 12:35 PM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,659
Thanks: 397
Thanked 634 Times in 615 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Hide set of columns on selection - a tough one ques for me

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
Code:
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 02-06-2019 at 12:48 PM.
Gasman is offline   Reply With Quote
Old 02-09-2019, 10:14 AM   #3
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Hide set of columns on selection - a tough one ques for me

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..
Attached Images
File Type: jpg Tbl.jpg (23.0 KB, 21 views)

Ashfaque is offline   Reply With Quote
Old 02-09-2019, 10:26 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,659
Thanks: 397
Thanked 634 Times in 615 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Hide set of columns on selection - a tough one ques for me

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.

Code:
    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
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 02-09-2019 at 10:36 AM.
Gasman is offline   Reply With Quote
Old 02-15-2019, 12:11 PM   #5
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Hide set of columns on selection - a tough one ques for me

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,
Attached Images
File Type: jpg Required Col To Hide and Display.jpg (64.2 KB, 17 views)
Attached Files
File Type: zip MD Frame 24 - 15-02-2019.zip (565.4 KB, 13 views)
Ashfaque is offline   Reply With Quote
Old 02-15-2019, 12:36 PM   #6
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,659
Thanks: 397
Thanked 634 Times in 615 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Hide set of columns on selection - a tough one ques for me

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-15-2019, 08:11 PM   #7
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Hide set of columns on selection - a tough one ques for me

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)


Code:
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

Code:
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,

Ashfaque is offline   Reply With Quote
Old 02-16-2019, 02:00 AM   #8
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,659
Thanks: 397
Thanked 634 Times in 615 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Hide set of columns on selection - a tough one ques for me

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-16-2019, 05:10 AM   #9
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Hide set of columns on selection - a tough one ques for me

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
Attached Files
File Type: zip MD Frame 24 - 16-02-2019.zip (567.8 KB, 14 views)
Ashfaque is offline   Reply With Quote
Old 02-17-2019, 03:14 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,659
Thanks: 397
Thanked 634 Times in 615 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Hide set of columns on selection - a tough one ques for me

That is because you are running this code

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
    For i = iShow + 1 To LastCol

        Columns(i).EntireColumn.Hidden = True

    Next
]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

Code:
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.
Attached Files
File Type: zip EPS MD Frame 24 - 16-02-2019.zip (528.8 KB, 15 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Ashfaque (02-22-2019)
Old 02-22-2019, 05:56 AM   #11
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Thumbs up Re: Hide set of columns on selection - a tough one ques for me

Thanks Gasman,

Really appreciate.

It is working as per requirement...

Excellent....

Ashfaque is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide/Unhide columns WineSnob Forms 0 12-04-2012 08:40 AM
Datasheet show and hide columns Laurad Forms 0 02-14-2011 10:05 AM
Auto Hide Columns amerifax Excel 7 08-04-2009 11:11 AM
hide columns in a report before printing krunalprajapati Reports 2 07-10-2005 03:26 PM
hide columns BUT not in a combo box craw Forms 15 09-29-2003 11:25 AM




All times are GMT -8. The time now is 10:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World