Learn Excel VBA By Index (E)

End Method

You can use the End of the Range object to select a particular cell. See this example...

Sub EndDemo ()

Range(ActiveCell, ActiveCell.End(xlDown)).Select
'select downward the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlUp)).Select
'select upward the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
'select to the left of the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlToRight)).Select
'select to the right of the activecell to last non-empty cell

End Sub



Err

The default syntax of Excel VBA. This example demonstrates error handling by jumping to a label..

Sub ErrDemo ()

Dim x As Integer

On Error GoTo ErrorHandler

x = "abc"

Exit Sub

ErrorHandler:

e = Err.Number & " ...cannot assign integer value to x"

MsgBox (e)

End Sub



Exit Do

If you need to jump out from a Do loop, you can do this by using the Exit Do statement. The macro below will do the trick.

Sub ExitDoDemo ( )

x = 1

y = 100

Do While x <>

If x^2 > 100 Then Exit Do

x = x + 1

Loop

End Sub

The procedure will halt when x^2 is more than 100.



Exit Sub

The Exit Sub statement is use as a point of exiting a subroutine with running the rest of any other statement in a procedure. The example below will display a message box to prompt the user whether to continue or not. If user choose yes, the subroutine will call the macro MyProcdure and will exit the current subroutine.

Sub ExitSubDemo ( )

msg = "Do you want to continue?"

answer = MsgBox(msg,vbYesNo)

If answer = vbYes Then

Call MyProcedure

Exit Sub

Else

MsgBox "Program will end now."

End if

End Sub