Excel VBA Macro

This handy VBA procedure is just to show you how we can calculate and display someone Age. To do this, of course we need to know their date of birth first, then simply using datediff and mod vba functions we can calculate how old is he/she.

Sub PersonAge()
    Dim iYears As Integer, iMonths As Integer, iMonth As Integer
    Dim dtDateofbirth As Date

    dtDateofbirth = _
    CDate(InputBox("Input your date of birth (yyyy-mm-dd)", "Date of birth"))

    iYears = DateDiff("yyyy", CDate(dtDateofbirth), Date)
    iMonths = (DateDiff("m", CDate(dtDateofbirth), Date)) Mod (iYears * 12)

    MsgBox "Your age are " & iYears & " year(s) and " & iMonths & " month(s)"
End Sub

For this example, I use MsgBox and InputBox to get input and display the output data, but we can change it to worksheet cells reference if we want to integrate this calculation in our Excel Worksheet.

We can get an input from user using Input Box, but we also can use the Input Box to get the address of cell reference in Microsoft Excel.

Using simple Excel VBA procedure below we can ask user to select range of cells (as copy input) and where they want it paste.

Sub CopyPaste()

Dim InputCells As Excel.Range
Dim OutputCells As Excel.Range

On Error Resume Next

'Show input box to get range of cells that want to copy
Set InputCells = _
Application.InputBox(Prompt:="Block input cells/range", _
Title:="Copy Paste", Type:=8)

'Show input box to get where they want it paste
Set OutputCells = _
Application.InputBox(Prompt:="Select cell where you want paste it", _
Title:="Copy Paste", Type:=8)

'Copy range of input cells
InputCells.Copy

'Paste it into output cells reference
OutputCells.PasteSpecial (xlPasteAll)

End Sub

Run the excel macro, when the first input box show up, block range of cells that we want to copy, click OK, and when the second input box showed, choose destination cell and click OK, then the source cells will be copied into the destination cell.

UPDATE June 13, 2008:

Another alternative way to find the last row with data :

Function LastRow() As Long

    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    LastRow = ix

End Function

I did a simple test, and the function will return the correct last row position even when the data was filtered

Read also the opposite version of this function, instead of row, in here we find the last column containing data.

UPDATE Oct 25, 2008:

As you can see there are several way to find the last row that contain data in Excel, which you can found in the post comments below, please feel free to read it.

Today, when I trying to solve another problem of mine, I found more simple approach to find the last raw.

As you probably know, Excel has a special cell like blank cell, cell containing formula, cell with comments etc, let me quote it from Excel help.

xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

So, I think why not we use Excel special cell type xlCellTypeLastCell to find the row position of that LastCell? Isn’t the row position of last cell in the used range will be the same with the last row that contain data in Excel?

I then create a dummy sheet, fill it with data, and run this simple vba code, and yes, the vba code return the right position of last row with data in corresponding worksheet.

'
Cells.SpecialCells(xlCellTypeLastCell).Row
'

But unfortunately, again, this vba code won’t work on filtered Excel Worksheet, the code will return position of the last row which is not filtered.

Please try it, and leave me a comment.

ORIGINAL:

This one is my fav, I commonly used this simple Excel VBA function to get the last row on active worksheet that contain any kind of data.

The function will return the position of the last row, so we can assume that after that row, there are no other data on the active worksheet.

Public Function GetLastRowWithData() As Long

    Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long

    Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
    lLastDataRow = ExcelLastCell.Row
    lRow = ExcelLastCell.Row
    '
    Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    '
    lLastDataRow = lRow

    GetLastRowWithData = lLastDataRow

End Function

We can call this formula from the cell formula bar and return the position of the last row with data, or we can also call it from others function or procedure (excel macro).

The most common use of this formula is when I need to paste some data to the next row after the last row that contain any data on my Excel sheet, and this formula really help to find the right location to paste it.

FIN.

Ceiling, or changing a specific decimal number into become its upper number, for example if we ceiling 3.25 we will got 4, or if we ceiling 2.75 then we will got 3 as a result.

To get a result like that in Microsoft Excel, we can simply use this very simple but neat Excel VBA function

Function Ceiling(Number As Double) As Long
    Ceiling = -Int(-Number)
End Function