Sub DelblankDE()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlManual
For i = LastRow To 1 Step -1
If IsEmpty(Cells(i, 4).Value) And IsEmpty(Cells(i, 5).Value) Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
Sub test2()
With Columns("D:E")
.AutoFilter field:=1, Criteria1:="="
.AutoFilter field:=2, Criteria1:="="
.Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
.AutoFilter
End With
End Sub
mon test.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ||||||
2 | LTPBY301 | |||||
3 | 11.19 | 2N04 | ||||
4 | 06:59 | 2N09 | ||||
5 | ||||||
6 | DOO | 5W52 | ||||
7 | DOO | 5W52 | ||||
8 | 1W52 | |||||
9 | ||||||
10 | 2K51 | |||||
11 | 2K88 | |||||
12 | ||||||
13 | ||||||
14 | ||||||
15 | ----------------------------------------------------------------------------------------- | |||||
16 | LTPBY302 | |||||
17 | 4.15 | DOO | 5K08 | |||
18 | 12.29 | |||||
19 | 5K08 | |||||
20 | SX | 2K08 | ||||
21 | 09/07/2007 | 2N13 | ||||
22 | 07/09/2007 | |||||
23 | 1N64 | |||||
24 | 1N89 | |||||
25 | DP | |||||
Sheet1 |
mon test.xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | LTPBY301 | 2N04 | 4.36 | Bletchley | Euston | |||
2 | LTPBY301 | 2N09 | 5.55 | Euston | Nhampton | |||
3 | LTPBY301 | Nhampton | Nhampton | |||||
4 | DOO | 5W52 | 08+06 | Nhampton | NhampNJ | |||
5 | DOO | 5W52 | 08+15 | NhampNJ | Nhampton | |||
6 | 1W52 | 8.24 | Nhampton | Euston | ||||
7 | Euston | Euston | ||||||
8 | 2K51 | 9.54 | Euston | MKeynes | ||||
9 | 2K88 | 11.15 | MKeynes | Bletchley | ||||
10 | Bletchley | |||||||
Sheet1 |
Sub DelblankDE()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlManual
Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
For i = LastRow To 1 Step -1
If IsEmpty(Cells(i, 4).Value) And IsEmpty(Cells(i, 5).Value) Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub