How's this?
Code:
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+.
Bookmarks