Sub showNameOnly()

searchKey = “\”
Path = “C:\Test\Sample\Job\KentLau”
lastSlashPosition = InStrRev(Path, searchKey, -1)
MsgBox Len(Path) - lastSlashPosition
MsgBox Right(Path, 7)

End Sub

Sub showFileName()
MsgBox FileDateTime(ActiveWorkbook.FullName)
End Sub

txtEndDate.Value = DateAdd(”yyyy”, 5, txtStartDate.Value)

how to add 7 days from today date

assume: txtStartdate, txtEndDate are TextBox

txtEndDate.Value = DateValue(txtStartDate.Value) + 7

You can further format it e.g. 20-Apr-09

Format(txtEndDate.Value, “dd-mmm-yy”)

等待是一种乐趣

这个年代，我们是越来越戁做到等待了

因为觉得等待太浪费时间了

所以我也迫不及待的上传这篇短文

慢有什么好？

快又有什么好？

早餐要吃快一点

车应该开快一点

话要讲快一点

当我们不再等待， 不再“浪费”时间，我们也同时失去。。。乐趣

要种一棵树

现代人马上问“要多久才能结果？”

而不是问“怎样让种树更有趣？”

时间不够用啊

等不及了

我们要的是，只是

成长的速度还是成长所带来的一切

要稳健的成长还是

一飞冲天的瞬间成长，瞬间消失？

我们都忘记了那个才更重要

Suppose you have data at Column A to column D for your Sales department.

then you can use the following code:

Columns(”A:D”).EntireColumn.AutoFit

You want to copy a range of cell and paste it to another sheet.

you have two sheets named as sourceSheet and destinationSheet

Use the following code:

Sheets(”sourceSheet”).select

Range(”A1:F1″).select

Selection.Copy

Sheets(”destinationSheet”).select

Activesheet.Paste

You want to analyze and know the total number of “XYZ” exist in Column B

Use the following VBA code:

Sub GetNumOfXYZ()
searchKey = “XYZ”
Set rng = Columns(”B”)
MsgBox WorksheetFunction.CountIf(rng, searchKey)
End Sub

Msgbox Activesheet.Rows.Count

How to apply?

You can use this to find the last row of the UsedRange

Range(”A” & ActiveSheet.Rows.Count).End(xlUp).Select

Sometime you want to ask your user to close a workbook if the workbook is not used in 10 minutes, the following should help:

Sub autoClosed()
RunWhen = Now + TimeValue(”00:00:10″)
Application.OnTime RunWhen, “closedNow”, , True
End Sub

Sub closedNow()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub