- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
29#
發表於 2013-9-12 11:22
| 只看該作者
回復 28# owen9399 - Private Sub CommandButton3_Click() '查詢
- Dim I As Integer, Rng As Range
- With Sh(2)
- .AutoFilterMode = False
- For I = 1 To UBound(ar)
- If ar(I) <> "" Then .Range("A1").AutoFilter I, ar(I)
- Next
- '.Range("A:i").SpecialCells(xlCellTypeVisible).Copy .Range("AA1") ->'Sh(2)的 AA1->AI欗
- '******* .ColumnCount = 8 'ListBox1設定8欗 所以改成如下********
- .Range("A:H").SpecialCells(xlCellTypeVisible).Copy .Range("AA1")
- .AutoFilterMode = False
- Set Rng = .Range("AA1").CurrentRegion.Offset(1) 'Sh(2)的 AA1->AH :AI欗的資料,*****AI欗須先刪掉(手動)*****
- End With
- ListBox1.RowSource = Rng.Address
- End Sub
- Private Sub CommandButton4_Click() '刪除整列
- Dim s, E As Range, I As Integer
- With ListBox1
- If .ListIndex = -1 Then MsgBox "沒有選擇!!": Exit Sub
- s = Application.Index(Application.Transpose(Application.Transpose(.List)), IIf(.ListCount = 1, 0, 1))
- If Join(s, "") = "" Then MsgBox "沒有資料!!": Exit Sub
- s = Application.Index(Application.Transpose(Application.Transpose(.List)), IIf(.ListCount = 1, 0, .ListIndex + 1))
- 'S= ListIndex的選擇.ListIndex 那一列的資料,會比 Resize(, 8)多一欗.
- '當
- '->.Range("A:i").SpecialCells(xlCellTypeVisible).Copy .Range("AA1") ->'Sh(2)的 AA1->AI欗
- '-> For Each E In .Range("A1", .Range("A1").End(xlDown)).Resize(, 8).Rows '整列:[ 自動編號......業務員 ]
- End With
- s = Join(s, ",") 'S: 結合控制項的字串 [ 自動編號序號公司配額筆數數值應付已付(進貨數量1+進貨數量2)業務員 ]
- With Sh(2)
- For Each E In .Range("A1", .Range("A1").End(xlDown)).Resize(, 8).Rows '整列:[ 自動編號......業務員 ]
- Debug.Print s
- Debug.Print Join(Application.Transpose(Application.Transpose(E)), ",")
- If s = Join(Application.Transpose(Application.Transpose(E)), ",") Then
-
- If MsgBox(Join(Application.Transpose(Application.Transpose(E.Value)), ","), vbYesNo, "刪除列") = vbYes Then
- 處裡刪除整列 E
- End If
- End If
- Next
- End With
- CommandButton3_Click '重新查詢
- End Sub
|
|
發表於 2013-10-8 14:59
| 
