1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| Sub test()
'
' test Macro
'
Dim Ws As Worksheet
Dim i As Integer
Dim NomFichier As String
Dim Wk As Workbook
NomFichier = Application.GetOpenFilename
Set Wk = Workbooks.Open(NomFichier)
MsgBox "You choosed the file named : " & Wk.Name
Set Ws = Wk.Worksheets(5)
For i = Ws.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
MsgBox i
'Rows.count return the number of lines of the range selected
'If the column is not empty, and his offset(0,1) or offset(0,3) is empty
If Not IsEmpty(Ws.Range("A" & i).Value) And (IsEmpty(Ws.Range("A" & i).Offset(0, 1).Value) Or IsEmpty(Ws.Range("A" & i).Offset(0, 3).Value)) Then
'remove the ligne
Ws.Rows(i).EntireRow.Delete
'If the column is empty, and his offset(0,1) or offset(0,3) is not empty
ElseIf IsEmpty(Ws.Range("A" & i).Value) And (Not IsEmpty(Ws.Range("A" & i).Offset(0, 1).Value) Or Not IsEmpty(Ws.Range("A" & i).Offset(0, 3).Value)) Then
'remove the ligne
Ws.Rows(i).EntireRow.Delete
'If the column is empty, and his offset(0,1) or offset(0,3) is empty
ElseIf IsEmpty(Ws.Range("A" & i).Value) And (IsEmpty(Ws.Range("A" & i).Offset(0, 1).Value) Or IsEmpty(Ws.Range("A" & i).Offset(0, 3).Value)) Then
'remove the ligne
Ws.Rows(i).EntireRow.Delete
End If
Next i
End Sub |
Partager