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 [...]

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 [...]

Ever needed to find out the full path of your current file? What I mean by full path here is the full location of the active file including the directory path and full file name. If you did, then what you need is this following code: Application.ActiveWorkbook.FullName The one line code will return the full [...]

The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words. Function Acronym(Words As Variant) As String Dim aWord() As String, ix As Integer aWord = Split(Words.Value, ” “) For ix = 0 To UBound(aWord) Acronym = Acronym [...]

There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base. Here a simple example of [...]

Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date. The algorithm is like this: I’ll create a loop from [...]

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 [...]