Positioning a Column on the Screen

Thanks @Willy Vanhaelen
I understand the usage of the with block when referring to a number of properties; I thought I was missing something with only one statement.

Cheers
Tom

@tom
In Escel's VBA Editor Press [F1] and type With: you get ample information.

You are quite right that in the second macro it has no sence to use With ... End With.

Sub GotoCol2()
        ActiveWindow.FreezePanes = False
        Range("H1").Select
        ActiveWindow.FreezePanes = True
        Application.Goto Reference:=Range("Z1"), Scroll:=True
End Sub

is exactly the same as:

Sub GotoCol2()
    With Application
        ActiveWindow.FreezePanes = False
        Range("H1").Select
        ActiveWindow.FreezePanes = True
        .Goto Reference:=Range("Z1"), Scroll:=True
    End With
End Sub

But in this example for instance it is beneficial to use With ... End With:

With Selection.Offset(0, -1)
    If Not .Comment Is Nothing Then
        A = MsgBox(.Comment.Text, vbOKCancel, "Delete comment?")
        If A = vbOK Then .Comment.Delete
    End If
End With

Although you have 2 lines extra the lines between With and End With are much shorter and easier to write and the code executes slightly faster.

What is the reason for the with block, containing statements not relating to the Application object.

In the second example, only one method of the object is used, possibly making the with block redundant.

Is there any benefit to this?

If, for example, column C is the leftmost visible column when this macro is run, columns A and B remain unseen. It would seem that

.Goto Range("A1")

is needed before selecting H1 to ensure that column A is included in the freeze pane. Particularly if one is freezing some rows at the top as well.

...Huh, I didn't know GoTo Range was a thing. Nice to know.

Like Gary I have used ScrollColumn in the past, but this sounds faster.

One slight variation on this that I have found really helpful is to combine the scroll function with a named cell (in the example below, cell Z1 is named "NAME").

This means that you can ensure the right cells are shown, even if additional columns are inserted into the sheet (so if the information you had in Z is now in AA or later).

ActiveWindow.FreezePanes = False
Range("H1").Select
ActiveWindow.FreezePanes = True
myCol = Range("NAME").Column
ActiveWindow.ScrollColumn = myCol