比如在SHEET1中的A列里有 1,2,3等数据
SHEET2中的A列里有 4,5,6等数据
于是我在SHEET3、SHEET4....中的A列输入1,2,3,4,5,6等数据时会有提示重复
也就是说,整个工作簿内任何一个A列的数据都不会重复
If Target.Column = 1 Then
For Each st In ActiveWorkbook.Sheets
n = n + Application.WorksheetFunction.CountIf(st.Columns("A"), Range("A" & Target.Row))
Next st
If n >= 2 Then Target.Clear
End If
End Sub
æalt+f11ï¼åå»å·¦ä¾§çthisworkbookï¼å¾å ³é®ï¼å¿ 须添å å¨workbookä¸ï¼æ·»å å°ä»»æä¸ä¸ªsheetéé½ä¸è¡ï¼ æ·»å 以ä¸ä»£ç å³å¯ã
ä½ç¨æ¯ï¼å¦æAåæéå¤ï¼åèªå¨æ¸ é¤åè¾å ¥çæ°æ®ã
å¦æè¦æ¹æå¼¹åºå¯¹è¯æ¡æ示ï¼é£ä¹æåæ°ç¬¬ä¸è¡çTarget.Clearæ¹æ msgbox "æéå¤"å³å¯è¿½é®
æè°¢æ¨ç帮å©ï¼çç¡®æææï¼ä½æ¯è¿æä¸ç¹å°é®é¢
å°±æ¯å¨å¤ä¸ªæ°æ®æ¹éå¤å¶è¿å»çæ¶åä¼å¤±æ
æ¯å¦SHEET1çAåä¸æ AãBãCãDï¼ä½æ¯å½æä»å¦å¤ä¸ä¸ªâå·¥ç»ç°¿âå¤å¶EãAãBãCãDè¿å
¥SHEET1ä¸æ¶ï¼å°±ä¸ä¼èªå¨å»éäºãæå¸æå¾å°çæ¯å¯ä»¥èªå¨å æéå¤çæ°æ®èè½ä¿çå
¶ä¸ä¸éå¤çæ°æ®ï¼å¾å°æç»çç»æAãBãCãDãE
æ£å¨ä¿®æ¹,è¿æ ·å,æé®é¢ä¸æ¥å°ä½å§,æ没æå¯è½å¤å¶è¿å»çæ°æ®ä¸ä»
ä»
æ¯Aå,æ¯å¦è¿å
æ¬ABCä¸åè¿æ ·,ç¶åéè¦æéå¤çè¿ä¸æ´è¡æ°æ®é½å é¤æ?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 1 Then
For rag = Target.Rows.Count To 1 Step -1
For Each st In ActiveWorkbook.Sheets
n = n + Application.WorksheetFunction.CountIf(st.Columns("A"), Range("A" & (Target.Row + rag - 1)))
Next st
If n >= 2 Then Range("A" & (Target.Row + rag - 1)).Clear
Next rag
End If
End Sub
è¿æ ·å°±æ¯ä»
ä»
æ¸
é¤Aåéå¤çæ°æ®,对å¤å¶ææ,å¦æè¦æ¹æå é¤Aéå¤æå¨çæ´è¡,é£ä¹å°±æåæ°ç¬¬ä¸è¡thenåè¾¹çé¨åæ¹æ rows(Target.Row + rag - 1).delete
å¤è°¢å
å¼ä½ è´¹å¿äºï¼ææ¿ä¸å°½
使ç¨äºä¸ä¸ä¹ååç°æ2个é®é¢
1.æ¯å¦SHEET1ä¸AåæAãBãCãD æåç¬ä»å
¶ä»å°æ¹å¤å¶è¿å»ä¸ç»æ°æ®ï¼AãBãCãDãEãFãG åç°é¤äºAãBãCãD ä¼è¢«å é¤ä»¥å¤,EåFä¹ä¼æ¶å¤±ï¼åªå©Gï¼æ¢åªå©æåä¸ä¸ªæ°æ®ï¼
2.ææåæ¬åå¨çå
容å é¤æåï¼éå®DELETEï¼ï¼ç¶åå¨æ³è¾å
¥å æçå
容就å ä¸è¿å»äºï¼ä¸ç¥å¯å¦è§£å³
æç忽äº,å¤æéå¤çnå¿è®°å ä¸ä¸ªéç½®è¯å¥äº.æ¹ä¸ºä¸è¾¹è¿ä¸ªå³å¯
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 1 Then
For rag = Target.Rows.Count - 1 To 0 Step -1
n = 0
For Each st In ActiveWorkbook.Sheets
n = n + Application.WorksheetFunction.CountIf(st.Columns("A"), Range("A" & (Target.Row + rag)))
Next st
If n >= 2 Then Range("A" & (Target.Row + rag)).Clear
Next rag
End If
End Sub
è³äºè¾å
¥å æçå
容,åªè¦Aåä¸éå¤,ä¸ä¼å ä¸è¿å»çå.