EXCEL TOOLS
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
[code]
และปิดด้วย [/code]
ตัวอย่างเช่น [code]dim r as range[/code]
เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)Code: Select all
Sub update()
'
'
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-27
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$60").AutoFilter Field:=1, Criteria1:=Array( _
"490407", "490435", "490477", "490485", "490513", "490601", "490604", "490606", _
"490610", "490613", "490617", "490620", "490621", "490624", "490707", "490730", _
"490742", "490743"), Operator:=xlFilterValues
Range("A21").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Main").Select
ActiveWindow.SmallScroll Down:=-9
Range("A3").Select
ActiveSheet.Paste
Range("C6").Select
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-27
Range("C21").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B33").Select
ActiveWindow.SmallScroll Down:=-21
Sheets("Main").Select
Range("C3").Select
ActiveSheet.Paste
Range("L8").Select
End Sub
Code: Select all
'Other code
ActiveSheet.Range("$A$1:$H$60").AutoFilter Field:=1, Criteria1:="490*"
'Other code
Code: Select all
Sub KeepData()
'
'
Sheets("Main").Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Detail").Select
ActiveWindow.SmallScroll Down:=3
Range("A20").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=9
Range("A35").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C35").Select
Sheets("Main").Select
ActiveWindow.SmallScroll Down:=-9
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("G17").Select
Sheets("Detail").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J32").Select
ActiveWindow.SmallScroll Down:=-30
Range("K3").Select
End Sub
Code: Select all
Sub KeepData()
'
'
Sheets("Main").Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Detail").Select
' ActiveWindow.SmallScroll Down:=3
' Range("A20").Select
' Selection.End(xlDown).Select
' ActiveWindow.SmallScroll Down:=9
' Range("A35").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Range("C35").Select
Selection.Offset(0, 2).Select
Sheets("Main").Select
' ActiveWindow.SmallScroll Down:=-9
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
' Application.CutCopyMode = False
Selection.Copy
' Range("G17").Select
Sheets("Detail").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J32").Select
' ActiveWindow.SmallScroll Down:=-30
Application.CutCopyMode = False
Range("K3").Select
End Sub
Code: Select all
Sub update()
'
'
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-27
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
' ActiveSheet.Range("$A$1:$H$60").AutoFilter Field:=1, Criteria1:=Array( _
' "490407", "490435", "490477", "490485", "490513", "490601", "490604", "490606", _
' "490610", "490613", "490617", "490620", "490621", "490624", "490707", "490730", _
' "490742", "490743"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$H$60").AutoFilter field:=1, Criteria1:="490*"
ActiveSheet.Range("$A$1:$H$60").AutoFilter field:=1, Criteria1:="036300*"
ActiveSheet.Range("$A$1:$H$60").AutoFilter field:=1, Criteria1:="036600*"
Range("A21").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Main").Select
ActiveWindow.SmallScroll Down:=-9
Range("A3").Select
ActiveSheet.Paste
Range("C6").Select
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-27
Range("C21").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B33").Select
ActiveWindow.SmallScroll Down:=-21
Sheets("Main").Select
Range("C3").Select
ActiveSheet.Paste
Range("L8").Select
End Sub
Code: Select all
'Other code
ActiveSheet.Range("$A$1:$H$60").AutoFilter Field:=1, Criteria1:=Array("490*", "036*"), _
Operator:=xlFilterValues
'Other code