1.行列交互
=INDEX(A1:A9,COLUMN(A1))
2.削除警告不非表示
application.displayalerts =false
……
application.displayalerts =true
'3.多行挿入
Range("6:25").Insert Shift:=xlDown
'4.行削除
Worksheets("Sheet1").Rows(6).Delete
5.Map利用(●)
Set d = CreateObject("scripting.dictionary")
http://www.geocities.jp/cbc_vbnet/Scripting/dictionary.html#dictionary
6.Class类利用
和java类似,定义好了,可以用new
7.操作1行的Rang数据
Dim myRange As Range
Set myRange = Range("A1:M1")
myRange.Select
myRange.Cells(1) = 3
myRange.Cells(6) = 6
8.格式设定(合并、网格线、Cell色)
可"录制宏参照其代码
'9.取得最终行
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Select
Debug.Print Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'10. 多列拆分
Set myRange = Range("A12:A16")
'行番号は、Excelの実際番号ではなく相対番号です
myRange.Cells(1, 1) = "商品番号 , 商品名, 単価, 数量, 日付"
myRange.Cells(2, 1) = "003, みかん, 30, 8, 2008/5/12"
myRange.Cells(3, 1) = "001, りんご, 100, 5, 2008/5/12"
myRange.Cells(4, 1) = "005, すいか, 500, 1, 2008/5/12"
myRange.Cells(5, 1) = "002, ばなな, 50, 10, 2008/5/12"
Dim cellFormat(4) As Variant
cellFormat(0) = Array(1, xlTextFormat)
cellFormat(1) = Array(2, xlGeneralFormat)
cellFormat(2) = Array(3, xlGeneralFormat)
cellFormat(3) = Array(4, xlGeneralFormat)
cellFormat(4) = Array(5, xlYMDFormat)
myRange.TextToColumns _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Comma:=True, _
FieldInfo:=cellFormat
也可以用
' FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
'11. Sheet数据排序Sort、过滤Filter
AutoFilter: http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_autofilter.html
AuSort: http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_sort.html
也 可参照Excel帮助