Reuse a script to change the range to rows 1 and 3 only
 

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).Entire Row.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

Reuse a script to change the range to rows 1 and 3 only
 

I decided this script is too complicated and I used another one. I will
repost.

"Janis" wrote:

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).Entire Row.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

Reuse a script to change the range to rows 1 and 3 only
 

This is very simple code.

Sub deleteR1R3()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
If (LastRow = 1) And IsEmpty(Range("A1")) Then
Cells(3, "A").EntireRow.Delete
Cells(1, "A").EntireRow.Delete
End If
End

"Janis" wrote:

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).Entire Row.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