vba+code+collection(包括语句集200句).docVIP

如何将打开的工作簿单元格从未打开的表中取数1、Sub GETVALUE() Dim myData As Object Set myData = GetObject(C:\Documents and Settings\xyf\桌面\book1.xls) Workbooks(book2).Sheets(sheet1).Range(A1:A6).Value _ = myData.Sheets(sheet1).Range(A1:A6).Value End Sub 2、Sub getvlaue() Dim myData As Object Dim myfile As String myfile = C:\Documents and Settings\xyf\桌面\book1.xls Set myData = GetObject(myfile) Sheets(sheet2).Range(A1:A6).Value _ = myData.Sheets(sheet1).Range(A1:A6).Value End Sub 用命令打开一个工作簿Sub openfile() Workbooks.Open (C:\Documents and Settings\JinYiFeng\桌面\book1) End Sub 取得工作表内有数据的最后一行行数我们经常用到代码 UsedRange.Rows.Count 或 SpecialCells 方法 Dim iRow As Integer, iCol As Integer Sub UsedRow1() With ActiveSheet ? ?? iRow = .UsedRange.Rows.Count ? ?? iCol = .UsedRange.Columns.Count ? ???MsgBox LastRow:= iRow.Cells(iRow, iCol).SelectEnd With End Sub Sub UsedRow2() With ActiveSheet ??? iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row ? iCol = .Cells.SpecialCells(xlCellTypeLastCell).Column MsgBox LastRow:= iRow.Cells(iRow, iCol).Select End With End Sub 2、但有时候却得到错误的行数这是因为，我们可能的某些操作，比如，格式刷，使我们得到不是想要的答案，我们换End(xlup)试试，缺点是，得指明第几列 Sub UsedRow3() With ActiveSheet ?iRow = .[a65536].End(xlUp).Row ?? iCol = .[iv1].End(xlToLeft).Column ? MsgBox LastRow:= iRow.Cells(iRow, iCol).Select End With 3、使用另外一个方法： Sub UsedRow4() Dim irow as integer Dim icol as integer ???iRow = sheets(“sheet1”).[A1].currentregion.Rows.count ? ? iCol = sheets(“sheet1”).[A1].currentregion.columns.count End sub 4、相关问题：如何得到工作表内有数据的行数和列数？ Sub UsedRow4()Dim myRange As Range Dim RowCount As Integer, ColumnCount As Integer Dim FirstRow As Integer, LastRow As Integer ? ? Set myRange = Sheet1.UsedRange ? ? FirstRow = myRange.Cells(1, 1).Row ? ? LastRow = myRange.Rows(myRange.Rows.Count).Row ? ? FirstColumn = myRange.Cells(1, 1).Column ? ? LastColumn = myRange.Columns(myRange.Columns.Count).Column 取非空白列总数? For i = FirstRow To LastRow ? ?? ???If Application.Cou