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

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
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top