請教vba

speed

新手一名

下載 (96 KB)

2016-5-15 15:24

請問vba點樣整到可以將空白的行del

hkoj

1. Sub DeleteBlankRows2()
   
2. 
   
3. 'Deletes the entire row within the selection if _
   
4. 
   
5. On Error Resume Next
   
6. 
   
7. Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
   
8. 
   
9. On Error GoTo 0
   
10. 
    
11. End Sub

複製代碼

google 搵, 選A column再執行,  試過work

supergag

1. Sub test()
   
2. ThisWorkbook.Worksheets(1).Range("A:A").EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
   
3. End Sub

複製代碼

Worksheets內既係第幾張sheet，左至右係由1開始
Range內既A:A係邊一行，B行就改哂做B

speed

如果我空白果度有FORMULA 就用唔到果代碼? 可以點整

supergag

如果我空白果度有FORMULA 就用唔到果代碼? 可以點整
speed 發表於 2016-5-16 16:25

有野係入面點認呢...

暫時諗到sort左非空白出黎再copy，一係就copy一次之後貼上值係第二行度再做

speed

回覆 5# supergag


我試過呢, 如果用函數整的空白, eg IF(A1="","") 睇上去係空白,但有公式係度,就用唔到上面的vba, 就算copy 另頁value都唔當係空白

ed1

回覆  supergag


我試過呢, 如果用函數整的空白, eg IF(A1="","") 睇上去係空白,但有公式係度,就用唔到 ...
speed 發表於 2016-5-17 10:00

用先用這vba clearcontent 方法clear 所有 睇上去係空白，eg：IF(A1="","") 為真正空白，再用delete blank row 既 vba

1. Sub ClearCell()
   
2.     Dim Rng As Range
   
3.     Set Rng = ActiveSheet.Range("A1")
   
4. 
   
5.     Dim i As Long
   
6.     For i = 1 To 100
   
7.         If Rng.Cells(i,1) = "" Then
   
8.            Rng.Cells(i,1).ClearContents
   
9.         End If
   
10.     Next i
    
11. End Sub

複製代碼

綜合change to real blank cell and delete blank row ,HIGHLIGHT COLUMN A and run vba

1. Sub DeleteRow()
   
2. Application.ScreenUpdating = False
   
3.   Application.Calculation = xlCalculationManual
   
4. 
   
5.     Dim rng As Range
   
6.     Set rng = ActiveSheet.Range("A1")
   
7. 
   
8.     Dim i , ix As Long
   
9.     For i = 1 To 100
   
10.         If rng.Cells(i, 1) = "" Then
    
11.            rng.Cells(i, 1).ClearContents
    
12.         End If
    
13.     Next i
    
14. 
    
15.   
    
16.   Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    
17.   If rng Is Nothing Then
    
18.          GoTo done
    
19.   End If
    
20.   For ix = rng.Count To 1 Step -1
    
21. If Len(Trim(Replace(rng.Item(ix).Formula, Chr(160), ""))) _
    
22.          = 0 Then rng.Item(ix).Delete (xlUp)
    
23.   Next
    
24. done:
    
25.   Application.Calculation = xlCalculationAutomatic
    
26.   Application.ScreenUpdating = True
    
27. End Sub

複製代碼

speed

回覆 7# ed1


THX CHING