Need Help with Globally Automating View/Zoom/Fit selection

[dcampbe1]

I am in need of a global macro that allows me to "view/zoom/select/fit selection" selected columns. Ideally it would allow me to go to any sheet of any book, select the columns I want to autofit view, e.g., A, and click on a macro button to execute it.

I would be surprised if such a macro hasn't been written many times over but I am not able to find such a thing. Does anyone have an idea how I would find or write such a macro?

Thanks.

[Ken Puls]

Hi, and welcome to the forum!

I'm not quite sure I follow what you mean by autofit view. Are you saying that you want to automatically zoom into 200% (or something) when you select a group of cells and press the button? If so, why not use the zoom slider for that?

[dcampbe1]

Hi Ken,
What I wanted to do was 'auto fit select' specific columns with a single click instead of the four or five that are otherwise necessary. Excel provides lots of buttons but none for the 'fit selection' (View/zoom). In the meantime I cobbled together some snippets and actually got it to work better that I had planned.

Attached is the code I ended up with. I'm sending it for two reasons, one to show you what I wanted and the other to share in case others find it useful.

Pardon me if I have quoted the code properly. I don't use these forums often enough and don't know how to wrap code.

Sub ZoomMySheet()
' Created out of a need for my brother to zoom in on active columns only.
' This was created to perform View/Zoom/Fit column/Enger with one click
' of an icon on the tool bar. The number of columns to be included is specified
' as being the right most column with with a non-blank cell in the first row.
' David W. Campbell - August 1, 2011
Set MyActiveCell = ActiveCell                                 'save current cursor location
    iLastCol = Cells(1, 256).End(xlToLeft).Column             'find right most non-blank cell in first row
    Range(Columns(1), Columns(iLastCol)).EntireColumn.Select  'select columns to be focused on
    ActiveWindow.Zoom = True                                  'zoom to fit
    MyActiveCell.Select                                       'go back to original cursor location
End Sub

Thanks for the reply.

Dave

[maninweb]

Hi dcampbe1...

independantly from the code and Ken's answer; perhaps following suggestion can also be helpful: there is a button in the ribbon "View" for zooming into a selection. Please see screenshot below. Firstly select the columns or range you want to zoom and the click on the button.

Regards :-)

[dcampbe1]

Hi dcampbe1...

independantly from the code and Ken's answer; perhaps following suggestion can also be helpful: there is a button in the ribbon "View" for zooming into a selection. Please see screenshot below. Firstly select the columns or range you want to zoom and the click on the button.

Regards :-)

Thanks for the help but I'm using 2003 which because it has no appropriate toolbar button icon requires four or five clicks to get to that feature.

[Ken Puls]

Ah, gotcha. So you take that code, link it to a button on the toolbar and you're good to go?

[dcampbe1]

Ah, gotcha. So you take that code, link it to a button on the toolbar and you're good to go?

YES! I'm thrilled because I don't really know enough about what I'm doing to accomplish such a feat. I spent far less time finding the two or three main ingredients and cobbling them together than I spent querying the forums. I'm really excited about accomplishing my objective.

If you've studied or played with that routine at all, you'll note that it has a weakness of grabbing everything below the highlighted cell to the first non-blank cell. That's fine as long as the desired selection is followed by a blank. The workaround now is to add a blank line below the intended area but it'd be nice to be able, as an option, to select all of the desired cells, instead of having to insert a blank at the bottom of the intended area. I just don't know enough about VBA to distinguish between just a selected area and the are bound by a blank -- what do they call such an area anyway? I've forgotten.

Thanks for your interest.

[Ken Puls]

LOL! You'll love this... they call it a "Selection"

Here's the code to zoom to your selected cells:

Sub ZoomToSelection()
Activewindow.Zoom = True
End Sub

Yeah, seriously. That's it.

And to revert to normal:

Sub ClearZoom()
Activewindow.Zoom = False
End Sub

To add it to a button on the toolbar in Excel 2003 and earlier, you:

• Right click a toolbar and choose Customize
• Go to Commands --> Macros
• Drag the "Custom Button" to the toolbar you want your macro on
• Right click the button and choose "Assign Macro"
• Select the Macro and choose OK
• Click Close

That's pretty much it.

[dcampbe1]

What I'd really like to do is incorporate this into my original code and test for multiple lines of selection such that if only one cell is selected it would jump one way (to the original code) and if more than one cell is selected to switch to the other (the new logic). Or is that possible? -- for an expert, nothing's impossible, right?

[Ken Puls]

How's this?

Sub ZoomMySheet()
Dim MyActiveCell As Range
' Created out of a need for my brother to zoom in on active columns only.
' This was created to perform View/Zoom/Fit column/Enger with one click
' of an icon on the tool bar. The number of columns to be included is specified
' as being the right most column with with a non-blank cell in the first row.
' David W. Campbell - August 1, 2011
'save current cursor location
    Set MyActiveCell = ActiveCell
'if only one cell selected, select columns to be focussed on
    If Selection.Cells.Count = 1 Then
        With ActiveSheet
            .Range(.Columns(1), .Columns(.Cells(1, .Columns.Count).End(xlToLeft).Column)).EntireColumn.Select
        End With
    End If
'zoom to fit and go back to original cursor location
    ActiveWindow.Zoom = True
    MyActiveCell.Select
End Sub

If you're going to do any amount of coding, I HIGHLY recommend that you go into the Visual Basic Editor, go to Tools-->Options-->Editor and check the "Require Variable Declaration" box. This will make all new modules show up with the line "Option Explicit" at the top, forcing you to declare your variables. You didn't have any issues in your code, but when you're beginning/mucking around, it's really important to ensure things don't go sideways. (And for exisitng modules, just type Option Explicit as the very first line of any module.)

I've declared the variables here, shortened up your code a bit, and also added some pieces to ensure it's working on the right sheet and will be foreward compatible with Office 2007+.