Selected Answer
Hello buildxp and welcome to the forum,
This can easily be done using VBA.
First you will need to resize the columns and rows so they fill the screen. These sizes will be specific to your computer based on its screen size and resolution. If you use the file on a different computer the column width and row height will need to be adjusted.
Your group A is 6 columns and groups B & C are 5 columns so column width for B & C will be wider. On my laptop the column widths are: group A – 23.56 and groups B & C – 28.56; the row height is 31. I added a row at the top as a header row and somewhere to put a button for the macros (height 30)
Three simple macros are used to switch between groups.
Sub Scroll_To_GroupB()
ActiveWindow.SmallScroll ToRight:=6
Range("G1").Select
End Sub
Sub Scroll_To_GroupC()
ActiveWindow.SmallScroll ToRight:=5
Range("L1").Select
End Sub
Sub Scroll_To_GroupA()
ActiveWindow.SmallScroll ToLeft:=12
Range("A1").Select
End Sub
I have attached a sample file to show how this would work.
UPDATE Dec. 8th
I have attached a second file (Go To Group A-B-C - Ver 2.xlsm) which does the row and column resize automatically. There is one small glitch - if on a blank default worksheet there is a partial row and/or column visible it will be included in the count of what is visible and the resize will be slightly "off". Here is the code placed in the "ThisWorkbook" code window:
Private Sub Workbook_Open()
Dim RH As Double ' default height of rows on blank worksheet
Dim NewH As Double ' height to resize rows 1 to 11 to
Dim CW As Double ' default width of columns on blank worksheet
Dim NewW1 As Double ' width to resize columna A to F to
Dim NewW2 As Double ' width to resize columna G to P to
Dim VisR As Long ' number of rows visible on blank worksheet in the window
Dim Visc As Long ' number of columns visible on blank worksheet in the window
' unprotect workbook (if protected)
'ActiveWorkbook.Unprotect "your password"
Application.ScreenUpdating = False
' add a worksheet to get default row and column sizes from
Sheets.Add.Name = "Test"
' get default row height and calculate new height for rows 1 to 11
VisR = ActiveWindow.VisibleRange.Rows.Count
RH = Rows(1).RowHeight
NewH = VisR * RH / 11
' get default column width and calculate new width for group "A" and for "B" & "C"
Visc = ActiveWindow.VisibleRange.Columns.Count
CW = Columns(1).ColumnWidth
NewW1 = Visc * CW / 6 ' group "A"
NewW2 = Visc * CW / 5 ' group "B" & "C"
' resize rows 1 to 11 and columns "A" thru "P"
Sheets("Data").Select
Rows("1:11").RowHeight = NewH
Columns("A:F").ColumnWidth = NewW1 ' group "A"
Columns("G:P").ColumnWidth = NewW2 ' group "B" & "C"
' delete the added worksheet ("Test")
Application.DisplayAlerts = False
Sheets("Test").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
' reprotect workbook (if needed)
'ActiveWorkbook.Protect "your password"
' save the workbook
ActiveWorkbook.Save
End Sub
If this solves your question please mark my answer as Selected.
Cheers :-)
