J
Janis
I want to delete row 1 and 3 if column A is blank. This script checks the
whole sheet and deletes every row if column A is blank. I only want to
delete 1 and 3 if A is blank. For safety sake how can I change the range to
get the union of column 1 and 3 only and delete them? It shouldn't check row
2 only rows 1 and 3.
thanks,
-------------------code---------
Public Sub deleteRows1and3()
Dim rngToSearch As Range
Dim rng As Range
Dim rngToDelete As Range
With ActiveSheet
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
For Each rng In rngToSearch
If Trim(rng.Value) = "" Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub
whole sheet and deletes every row if column A is blank. I only want to
delete 1 and 3 if A is blank. For safety sake how can I change the range to
get the union of column 1 and 3 only and delete them? It shouldn't check row
2 only rows 1 and 3.
thanks,
-------------------code---------
Public Sub deleteRows1and3()
Dim rngToSearch As Range
Dim rng As Range
Dim rngToDelete As Range
With ActiveSheet
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
For Each rng In rngToSearch
If Trim(rng.Value) = "" Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub