here is some useful reference:
--------------------------------------------
用VBA得到EXCEL表格中的行数和列数
每种方法中上面的是Excel的行数，下面的是Excel的列数。方法1:
ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count
缺点：有时可能会比实际数大一些，原因是如果你把最后几行（列）数据清除后（非整行或整列删除），用这个命令仍返回未清除前的值。就是说现在虽然是空的，但是你曾经用过也算你的。
方法2:
ActiveSheet.Range("A65535").End(xlUp).Row
ActiveSheet.Range("IV1").End(xlToLeft).Column
可以简写为：
ActiveSheet.[A65536].End(xlUp).Row
ActiveSheet.[IV1].End(xlToLeft).Column
缺点：只能计算出一列（行）的最后一个单元格所在的行（列）数。本例是只返回A列最后一个单元格所占的行数。
方法3:
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
缺点：在工作表进行对删除或清除操作时也会变得比实际情况大。
方法4:
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
缺点：在工作表进行对删除或清除操作时也会变得比实际情况大。
方法5：
Application.CountA(ActiveSheet.Range("A:A"))
Application.CountA(ActiveSheet.Range("1:1"))
只能统计一列（行）的实际使用情况，得到的不一定是最后一行（列）的位置。方法2的数值比此方法大时，说明在A列的数据间有空白未填写的单元格。
方法6：
ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
效果同方法2
以上方法中比较常用的是方法1和方法2。
from: http://www.okexcel.com.cn/bbs/viewthread.php?tid=26
-----------------------------------------------
the following is my code:
1. generate Ncase sheets and import
2.generate equations and combine
Option Explicit
Sub Button1_Click()
Dim nsheet As Integer, ncase As Integer, i As Integer, nrwst As Integer, nsheet As Integer
Dim connstring As String, fpath As String, exnm As String
nsheet = Sheets.Count
Worksheets("Instruction").Select
ncase = Cells(6, 3).Value
fpath = Cells(6, 7).Value
If fpath = "" Then
fpath = Application.Path & "\Result_Case"
End If
exnm = Cells(6, 9).Value
nrwst = Cells(10, 3).Value
If nsheet < ncase + 1 Then
Sheets.Add after:=Sheets(Sheets.Count), Count:=(ncase - nsheet + 1)
End If
For i = 1 To ncase
Worksheets(i + 1).Name = "Case" & i
Worksheets(i + 1).Select
connstring = "TEXT;" & fpath & i & exnm
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("$A$1"))
.Name = "Result_Case" & i
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = nrwst
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileFixedColumnWidths = Array(27)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next i
End Sub
Sub Button2_Click()
Dim nrow As Long, i As Integer, ncase As Integer
Dim eqstr As String
Dim ws As Worksheet
Worksheets("Instruction").Select
ncase = Cells(6, 3).Value
eqstr = Cells(23, 7).Value
For i = 1 To ncase
Worksheets(i + 1).Select
nrow = ActiveSheet.Range("a65536").End(xlUp).Row
Range("c2").Select
ActiveCell.FormulaR1C1 = eqstr
Range("C2").Select
Selection.AutoFill Destination:=Range("C2", Range("c2").Offset(nrow - 2, 0))
Next i
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Combined"
Set ws = Worksheets("Combined")
For i = 1 To 24
ws.Range(ws.Range("a1").Offset(0, (i - 1) * 3), ws.Range("a1").Offset(65535, i * 3 - 1)).Value = Worksheets("Case" & i).Range("a1:c65536").Value
Next i
End Sub
Sub deleteblank()
Dim i As Long, j As Long
Worksheets("CombinedDeletedMasked").Select
j = 1
For i = 3 To 72 Step 3
Worksheets("result").Cells(j, 1) = Cells(1048576, i)
j = j + 1
Next i
End Sub
没有评论:
发表评论