miss someone in the deepest part of my heart

雨落蕊中终远去
花萎云散思芳魂


I don't think you are likely to see this. It is just for my beautiful memory...
If we have next life (I would rather believe in Bhuda), I wish we can meet on the beautiful rose garden when we are young.

如何不复制隐藏的内容

中这个范围,按下F5,点"定位条件",点"可见单元格",再复制.

good on you , vba

i should spend some time reading the vba in excel. it really can do anything and becomes efficient (honestly i was not efficient today, learning how to use vba and want to develop a perfect program. NO never do that again, just let it running)


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

VBA Range

never really concentrated on the VBA although i can feel it is powerful.

i tried a pretty long time to copy 3 columns from 24 sheets to a new sheets. the code is as following. i know it may be stupid but it worked.

the RANGE is powerful with the offset. good. i will use this in later data processing.




Option Explicit
Sub CombineSheets()

Dim i As Integer
', j As Integer, k As Integer, nrows As Integer
'Worksheets("Combined").Select

Dim ws As Worksheet

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


'For j = 1 To 3
' For k = 1 To nrows
' Cells(k, (i - 1) * 3 + j) = Worksheets("Case" & i).Cells(k, j)
' Next k
'Next j
Next i

End Sub

Snapshot button in Acrobat X

I recently updated to Windows 7 on a new computer. everything is new, sometimes means frustrating. Probably it is true that we are involved in the Micro-Intel robbing game by the never stopping updating...

any complaints do not change the world. so the trouble is the Acrobat X. i can not find the familiar Snapshot (the camera) button . it really waste me a long time (Damn, or TNND in mother language). here is the solving method - simple?! (just not understanding what the software updating pig-head thinking).

right click the tool bar --> Edit -->Snapshot

a good reference: http://blogs.adobe.com/acrolaw/2011/03/customizing-toolbars-in-acrobat-x-2/