Excel VBA Macro

Get Position of Last Column Containing Data with Excel VBA

Posted on the June 13th, 2009 under Cells and Range by Poer @ Excel VBA/Macro

If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.

The VBA code is the exact replica of the code to get the last row, only this time, we change the row into column.

Function LastColumn() As Long

    Dim ix As Long
    ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    LastColumn = ix

End Function

The function above will return the appropriate position of the column even when the last column containing data in a hidden state.

The results of this function is the column number, not the column name (1 for A, 2 for B, etc). If what we need is the column name, then we can use the excel column number to column name converter that I wrote before.

Excel VBA Looping Tutorial

Posted on the December 1st, 2008 under Excel Tutorial by Poer @ Excel VBA/Macro

Do while … loop, do until … loop, do … loop while, do … loop until, and while … wend are the format of looping that we can use in Excel VBA.

The main reason why we need to perform looping is to simplified a repeating tasks. Rather then manually calling a block of code over and over again, it’s more simple and readable if we make it into loop.

There are two kind of loop

First, looping as long as the condition is true or false. This type of loop check the value of condition first, before the looping begin to start.

Second, repeat the task until the condition achieved. This type of loop will check the value of condition when the looping is finish.

Which one that we gonna pick is depend on our condition or purpose.

Syntax

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop

Or

Do
[statements]
[Exit Do]
[statements]

Loop [{While | Until} condition]

And

While condition
[statements]

Wend

While … wend is the same with do while … loop, and do while … loop is more structured and flexible compare to while … wend statement.

Examples:

' example of do while ... loop
Sub ChkFirstWhile()
    counter = 0
    myNum = 20
    ' do while myNum > 10
    ' we perform condition check before we do loop
    ' if the first check fail to meet the criteria
    ' no loop will be perform
    Do While myNum > 10
        myNum = myNum - 1
        counter = counter + 1
    Loop
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of do ... loop while
Sub ChkLastWhile()
    counter = 0
    myNum = 9
    ' do ... loop while myNum > 10
    ' we perform condition check after the loop
    ' we do at least 1 loop
    Do
        myNum = myNum - 1
        counter = counter + 1
    Loop While myNum > 10
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of do until ... loop
' the principle is the same with do while ... loop
Sub ChkFirstUntil()
    counter = 0
    myNum = 20
    Do Until myNum = 10
        myNum = myNum - 1
        counter = counter + 1
    Loop
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of do ... loop until
' the principle is the same with do ... loop while
Sub ChkLastUntil()
    counter = 0
    myNum = 1
    Do
        myNum = myNum + 1
        counter = counter + 1
    Loop Until myNum = 10
    MsgBox "The loop made " & counter & " repetitions."
End Sub

' example of while ... wend loop
Sub ChkWhile()
    Dim Counter
    Counter = 0    ' Initialize variable.
    While Counter < 20    ' Test value of Counter.
        Counter = Counter + 1    ' Increment Counter.
    Wend    ' End While loop when Counter > 19.
    Debug.Print Counter    ' Prints 20 in the Immediate window.
End Sub

FIN.