查看完整版本 : VBA點check範圍一問

想請問各位點可以用excel vba寫句check某個range以外是否有資料存在?
e.g. A1至Z1000係data，但我或人地多手唔知係AZD100099個cell入咗嘢但發現唔到，想check佢出嚟並delete晒呢啲以外(意外)嘅data?:smile_41:

[quote]原帖由 [i]alee001[/i] 於 2019-4-4 12:08 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497257989&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
想請問各位點可以用excel vba寫句check某個range以外是否有資料存在?
e.g. A1至Z1000係data，但我或人地多手唔知係AZD100099個cell入咗嘢但發現唔到，想check佢出嚟並delete晒呢啲以外(意外)嘅data?:smile_41: ... [/quote]

用for loop if cell<>a1 to z1000既野，如果cell<>"", cell=""

Cell曉唔曉報自己地址?

[quote]原帖由 [i]QQL0102[/i] 於 2019-4-5 07:52 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497321004&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]


用for loop if cell<>a1 to z1000既野，如果cell<>"", cell="" [/quote]


你意思係For Each cell <> Range("A1:Z1000") check晒一個sheet內由A1至XFD1048576咁多個cell?:funk:

:lol係.

其實我有初步寫法係每欄用.End(xlDown)去scan儲存格有冇內容...不過想問吓有冇其他方法:smile_13:

[quote]原帖由 [i]alee001[/i] 於 2019-4-5 10:04 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497326397&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
其實我有初步寫法係每欄用.End(xlDown)去scan儲存格有冇內容...不過想問吓有冇其他方法:smile_13: [/quote]
如果Cell曉報自己地址, 我就有方法.

[quote]原帖由 [i]111x111=12321[/i] 於 2019-4-5 10:18 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497326948&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]

如果Cell曉報自己地址, 我就有方法. [/quote]

冇架...就係想防止可以畀人用一個字鎖住worksheet插入欄位或列位。

*** 作者被禁止或刪除 內容自動屏蔽 ***

用Private Sub Worksheet_Change(ByVal Target As Range)係其中一個方法，但佢缺點係會嚴重拖慢運算，要係好多code不同位置放Application.EnableEvents = True/False控制先有改善...:smile_27:

其實我只想係Private Sub Workbook_open()開啟後check一次就夠...

*** 作者被禁止或刪除 內容自動屏蔽 ***

[quote]原帖由 [i]alee001[/i] 於 2019-4-5 09:10 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497324154&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]



你意思係For Each cell <> Range("A1:Z1000") check晒一個sheet內由A1至XFD1048576咁多個cell?:funk: [/quote]

將D cell 放入array,  check 完& delete 完，再將 array 放番入cell

[quote]原帖由 [i]alee001[/i] 於 2019-4-4 12:08 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497257989&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
想請問各位點可以用excel vba寫句check某個range以外是否有資料存在?
e.g. A1至Z1000係data，但我或人地多手唔知係AZD100099個cell入咗嘢但發現唔到，想check佢出嚟並delete晒呢啲以外(意外)嘅data?:smile_41: ... [/quote]

Disclaimer: 在下現在沒有 Excel, 只是靠記憶加 Google.
用 UsedRange property 可找到整個有 value 或改動過的 range.
如果在 A1..Z1000 之外便可以 check 那些 cells. 除非用家改了 range 外很遠的 cell, 不然很快便可處理完。
UsedRange 不只有資料的 cell, 任何改動過的 cell 都包括在內。

:smile_39:[quote]原帖由 [i]idontknowjack[/i] 於 2019-4-6 09:49 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497374255&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]


Disclaimer: 在下現在沒有 Excel, 只是靠記憶加 Google.
用 UsedRange property 可找到整個有 value 或改動過的 range.
如果在 A1..Z1000 之外便可以 check 那些 cells. 除非用家改了 range 外很遠的 cell, 不然很快便可處理完。
UsedRange 不只有資料的 cell, 任何改動過的 cell ... [/quote]

A1..Z1000作UsedRange只係一個例子方便說明，其實我個file內啲code含多個程序...經常須要變更不同位置、加減複製不同cell/range及公式，我已經用Application.Calculation = xlCalculationManual呢控制部分運算過程，所以唔係好似#11C兄咁講簡單用if過濾row同column，而且要控制邊個位用Worksheet_Change check會花好多時間改code(以前試過)...:smile_39:

[quote]原帖由 [i]alee001[/i] 於 2019-4-6 10:16 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497375684&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
:smile_39:

A1..Z1000作UsedRange只係一個例子方便說明，其實我個file內啲code含多個程序...經常須要變更不同位置、加減複製不同cell/range及公式，我已經用Application.Calculation = xlCalculationManual呢控制部分運算過程，所以唔係好似#11C兄咁講簡單用if過濾row同column，而且要控制邊個位用Work ... [/quote]

A1...Z1000 不是 usedRange. A1...Z1000 是先生認定「合法」的 range, 在此之外是你說要 delete 的。如果先生連自己那個 range 可以被改那個不可改都不清楚, 那不要說用 VBA 了，就算用人手也解決不了，不是嗎?

[code]Sub find_x()
Dim c As Single
Dim chk As Variant
c = 1
[a1] = Now
Do
chk = cells(1, c).End(xlDown).value
If chk <> vbNullString Then
If cells(1, c).End(xlDown).row <= 10000 Then
chk = cells(1, c).End(xlDown).Offset(10000, 0).End(xlDown).value
If chk <> vbNullString Then
MsgBox cells(1, c).End(xlDown).Offset(10000, 0).End(xlDown).address
End If
Else
MsgBox cells(1, c).End(xlDown).address
End If
End If
c = c + 1
Loop While c <= [az1].End(xlToRight).Column
[a2] = Now
End Sub[/code]如果設工作範圍為A1至AZ10000，check範圍以外幾個x位置，一張sheet約4min...
[attach]9519987[/attach]

[[i] 本帖最後由 alee001 於 2019-4-7 01:11 AM 編輯 [/i]]

以在下有限的認識，這 VBA code 做了不少多餘的動作。
先看 do...Loop, 每一個 pass 都做一次 [az1].End(xlToRight).Column 的動作，其實是多餘的重複。但是只看 az1 那行, 便漏看了其他行。比方説，如 bb1 有 value, program 便不會看 bc, bd, be,... 的 columns, 就算 (例如) bc1 有 value 也不會處理。
再看 loop 內的 logic, 重複的 xldown 就不多說了，但是  logic 只是找那 column 10000 行以後的第一個 value, 看來同一行的第二、三個 values 都不作處理。
usedRange 可以立刻告訴我們在 a1...az10000 外有沒有改動，clear 那些 cells 亦很快。但如果要特別處理可以簡單的 loop through 那些 cells, 或用 find method, 或者 specialcells, 又或者如先生用 xl + direction (不過用 up 和 left)可以快一點。

[quote]原帖由 [i]idontknowjack[/i] 於 2019-4-7 10:38 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497393909&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
以在下有限的認識，這 VBA code 做了不少多餘的動作。
先看 do...Loop, 每一個 pass 都做一次 [az1].End(xlToRight).Column 的動作，其實是多餘的重複。但是只看 az1 那行, 便漏看了其他行。比方説，如 bb1 有 value, program 便不會看 bc, bd, be,... 的 columns, 就算 (例如) bc1 有 value  ... [/quote]

多謝C兄提點，因為我唔知其他Excel版本column極限數目所以用[az1].End(xlToRight).Columng搵，佢只是一個值而已。改咗一段If cells(1,c).End(xlDown).row<=10000 And cells(1,c).Column<[az1].Column Then搵AZ欄以後嘅x。用x=ActiveSheet.UsedRange.Rows.count及x=ActiveSheet.UsedRange.Columns.count係可即check到最遠x位置，我會試吓改良...

"...其他Excel版本column極限數目..."

用 worksheet object 的 columns.count property 比較清楚和快。

重看先生 post #1, "...想check佢出嚟並delete晒呢啲以外(意外)嘅data?"  既然知道要保留的 range, 把這 range 之上、下、左、右的 ranges clear 或者 delete 走會不會簡單快捷一點?

[quote]原帖由 [i]idontknowjack[/i] 於 2019-4-8 02:29 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=497438209&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
"...其他Excel版本column極限數目..."

用 worksheet object 的 columns.count property 比較清楚和快。

重看先生 post #1, "...想check佢出嚟並delete晒呢啲以外(意外)嘅data?"  既然知道要保留的 range, 把這 range 之上、下、左、右的 ranges clear 或者 delete 走會不會 ... [/quote]

C兄說對。
我在想因為多餘data即使發現不了，但最終會因多次插入列數以沉底或插入欄數以靠極右，所以有個治表嘅方法係每次開檔進行清除頁內最後100行列及100個欄的內容更可解決鎖死插入的問題...

[quote]原帖由 [i]alee001[/i] 於 2019-4-4 12:08 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=497257989&ptid=28136323][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
想請問各位點可以用excel vba寫句check某個range以外是否有資料存在?
e.g. A1至Z1000係data，但我或人地多手唔知係AZD100099個cell入咗嘢但發現唔到，想check佢出嚟並delete晒呢啲以外(意外)嘅data?:smile_41: ... [/quote]

1. 將A1:Z1000copy
2. delete所有cells的內容
3. 貼回A1:Z1000
會不會簡單D

[quote]原帖由 [i]wawa2000[/i] 於 2019-5-7 05:17 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=498944194&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]


1. 將A1:Z1000copy
2. delete所有cells的內容
3. 貼回A1:Z1000
會不會簡單D [/quote]

你試過可以嗎?

可以用last cell.



    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select

會選擇當前worksheet有data的range內最右最底個cell.

如果想知有無就用二個cells寫, 數值大過0就有如果唔係就無。
=COUNTIF($A$1001 : $Z$1048576,">''")
=COUNTIF($AA : $XFD,">''")

改為就連數字都得

=COUNTIF($A$1001 : $Z$1048576,"<>"&"")
=COUNTIF($AA : $XFD,"<>"&"")

[[i] 本帖最後由 bickey 於 2019-5-11 09:27 AM 編輯 [/i]]

[quote]原帖由 [i]bickey[/i] 於 2019-5-10 03:33 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=499111065&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
如果想知有無就用二個cells寫, 數值大過0就有如果唔係就無。
=COUNTIF($A$1001 : $Z$1048576,">''")
=COUNTIF($AA : $XFD,">''") [/quote]

文字得數字唔得?

已做修正，謝。

[quote]原帖由 [i]bickey[/i] 於 2019-5-11 09:29 AM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=499145995&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
已做修正，謝。 [/quote]
知道但點清呢?

用control按鈕鍵選出兩個range按delete按鈕。

[quote]原帖由 [i]bickey[/i] 於 2019-5-11 03:45 PM 發表 [url=https://computer.discuss.com.hk/redirect.php?goto=findpost&pid=499163830&ptid=28136323][img]https://computer.discuss.com.hk/images/common/back.gif[/img][/url]
用control按鈕鍵選出兩個range按delete按鈕。 [/quote]

想要check完auto-delate。

ThisWorkbook > Workbook > Open
Private Sub Workbook_Open()
    Const cstrArea As String = "area"
    ActiveWorkbook.Names.Add _
        Name:=cstrArea, _
        RefersToR1C1:="=Sheet1!C27:C16384,Sheet1!R1001C1:R1048576C26"
    Application.Goto Reference:=cstrArea
    Selection.ClearContents
    ActiveWorkbook.Names(cstrArea).Delete
End Sub