Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
Hi Guys, I have data within columns a to d which is static. From Columns e onwards to say az there is a variety of information which needs to be entered on a row by row basis. It is rather time consuming to write macros to hide and unhide columns depedning on the view required. Is there any easier way of group (ie viewing) columns? (eg I want to see columns a - d, and h - k, for purposes of data entry 1, and then to enter data set 2, I need to see A - d and m - q), and all others hidden). Kind Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
In my old job, I had a similar problem. Although it may be rather kludge-y,
we ended up highlighting the various "views" with different background colors. Then I wrote a macro to hide or unhide rows and columns depending on the background color of the cell that was currently selected when the macro was run. In our situation, the different scenarios headers were found somewhere in rows 1 to 15, and the data entry started on row 16. You may need to adjust this code slightly to better fit your need. Hope it helps. Public Sub DoTemplate() Dim objWS As Excel.Worksheet Dim objCell As Excel.Range, objR As Excel.Range Dim I As Byte, K As Byte Dim J As Long Set objWS = ActiveWorkbook.ActiveSheet Set objCell = Application.Selection K = objCell.Row J = objCell.Interior.ColorIndex ' Hide rows For I = 2 To 15 If I < K Then objWS.Rows(I).Hidden = True End If Next ' Hide columns For I = 1 To 167 Set objR = objWS.Cells(K, I) If objR.Interior.ColorIndex < J Then objWS.Columns(I).Hidden = True End If Next Set objR = Nothing Set objCell = Nothing Set objWS = Nothing End Sub -- Hmm...they have the Internet on COMPUTERS now! "Darin Kramer" wrote: Hi Guys, I have data within columns a to d which is static. From Columns e onwards to say az there is a variety of information which needs to be entered on a row by row basis. It is rather time consuming to write macros to hide and unhide columns depedning on the view required. Is there any easier way of group (ie viewing) columns? (eg I want to see columns a - d, and h - k, for purposes of data entry 1, and then to enter data set 2, I need to see A - d and m - q), and all others hidden). Kind Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
Thanks - it may work :) - Im playing now... but if I want it to run on the sheet called master, do i need to change this line: Set objWS = ActiveWorkbook.ActiveSheet What would the syntax be...? Thanks, appreciate your assistance. Kind Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
hmm...Im almost there, (but as u can tell am no programmer!), could I have a bit more assistance.... in telling macro only to look at row 2's background colour (i dont want to highlight all the backround colours of the whole column....) What i have so far is: Public Sub DoTemplate() Dim objWS As Excel.Worksheet Dim objCell As Excel.Range, objR As Excel.Range Dim I As Byte, K As Byte Dim J As Long Set objWS = Sheets("master") Set objCell = Application.Selection K = objCell.Row J = objCell.Interior.ColorIndex ' Hide rows 'For I = 2 To 2 ' If I < K Then ' objWS.Rows(I).Hidden = True ' End If 'Next ' Hide columns For I = 2 To 52 Set objR = objWS.Cells(K, I) If objR.Interior.ColorIndex < J Then objWS.Columns(I).Hidden = True End If *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
If you're not looking at the sheet "Master" when you run the macro, then you
will need to change that line to something like: Set objWS = Activeworkbook.Worksheets("Master") (That would work even if you WERE looking at "Master" as well.) -- Hmm...they have the Internet on COMPUTERS now! "Darin Kramer" wrote: Thanks - it may work :) - Im playing now... but if I want it to run on the sheet called master, do i need to change this line: Set objWS = ActiveWorkbook.ActiveSheet What would the syntax be...? Thanks, appreciate your assistance. Kind Regards D *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
OK, if you want everything to be in row 2, then we can change a few things.
Note the comments I added within the code itself. Public Sub DoTemplate() Dim objWS As Excel.Worksheet Dim objCell As Excel.Range, objR As Excel.Range Dim I As Byte Dim J As Long Set objWS = Sheets("Master") ' Don't forget that wherever you're looking at, the selected cell needs to have a background ' color that matches the various "views" you've built on the master sheet. Perhaps ' have a chart, where users click a cell that has a "view" name, and that cell has a ' background color corresponding to the cells on the master sheet. Set objCell = Application.Selection J = objCell.Interior.ColorIndex ' Hide columns ' If columns A - D are always going to be there, we only need to start with column E, which ' is column number 5. For I = 5 To 52 Set objR = objWS.Cells(2, I) ' We'll always be looking at row 2 - the column will change If objR.Interior.ColorIndex < J Then objWS.Columns(I).Hidden = True End If Next Set objR = Nothing Set objCell = Nothing Set objWS = Nothing End Sub -- Hmm...they have the Internet on COMPUTERS now! "Darin Kramer" wrote: hmm...Im almost there, (but as u can tell am no programmer!), could I have a bit more assistance.... in telling macro only to look at row 2's background colour (i dont want to highlight all the backround colours of the whole column....) What i have so far is: Public Sub DoTemplate() Dim objWS As Excel.Worksheet Dim objCell As Excel.Range, objR As Excel.Range Dim I As Byte, K As Byte Dim J As Long Set objWS = Sheets("master") Set objCell = Application.Selection K = objCell.Row J = objCell.Interior.ColorIndex ' Hide rows 'For I = 2 To 2 ' If I < K Then ' objWS.Rows(I).Hidden = True ' End If 'Next ' Hide columns For I = 2 To 52 Set objR = objWS.Cells(K, I) If objR.Interior.ColorIndex < J Then objWS.Columns(I).Hidden = True End If *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
Thanks - works perfectly!!!! Much appreciated!!!! *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many columns
Darin,
You can create Views of the same WS and switch between those. e.g Hide/show initial columns. ViewCustom ViewsAdd.. Give it a name. Repeat for each you require. Then have a button or some means to switch to the next view. NickHK "Darin Kramer" wrote in message ... Hi Guys, I have data within columns a to d which is static. From Columns e onwards to say az there is a variety of information which needs to be entered on a row by row basis. It is rather time consuming to write macros to hide and unhide columns depedning on the view required. Is there any easier way of group (ie viewing) columns? (eg I want to see columns a - d, and h - k, for purposes of data entry 1, and then to enter data set 2, I need to see A - d and m - q), and all others hidden). Kind Regards D *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting 2 long columns into multiple columns in excel page and sor | Excel Discussion (Misc queries) | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming |