Deleting Blank Cells Subject to Criteria (Originally Posted in Excel by Mistake)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wanted to delete blank rows using the following:

Public Sub DeleteRowOnCell(
On Error Resume Nex
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delet
ActiveSheet.UsedRang
End Su

But I wanted instead of to check for an empty cell (as the above does?) to see if the following formula returned "" and delete the row if it did and to leave it if it didnt

=IF(ISERROR(VLOOKUP(B1,Sheet2!A1:A10,1,FALSE))=TRUE,"","1"

If anyone can help i'd sure appreciate it

Thanks in advanc

Mar
 
See the reply in the .misc group.

Mark said:
I wanted to delete blank rows using the following:-

Public Sub DeleteRowOnCell()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

But I wanted instead of to check for an empty cell (as the above does?) to
Click to expand...
see if the following formula returned "" and delete the row if it did and to
leave it if it didnt.
 
Key here is the use of Range.Value

Sub test()
Dim rngRow As Range, rngTemp As Range, rng As Range, bln As Boolean

For Each rngRow In Selection.EntireRow
bln = True
Set rngTemp = Intersect(ActiveSheet.UsedRange, rngRow)
If Not rngTemp Is Nothing Then
For Each rng In rngTemp
If Not rng.Value = "" Then
bln = False
End If
Next
If bln Then rngRow.Delete
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Mark said:
I wanted to delete blank rows using the following:-

Public Sub DeleteRowOnCell()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

But I wanted instead of to check for an empty cell (as the above does?) to
Click to expand...
see if the following formula returned "" and delete the row if it did and to
leave it if it didnt.
 
Thanks for the info, it works grea

I had a couple of other questions

1 How would I modify the formula to use the selection rule to delete rows that dont meet the criteria even if they are not blank. (an if possible a brief explanation as to how the change(s) work

2 A good beginners guide to Macro Programing

Thanks in advanc

Mark
 
Back
Top