more row deleting

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

hi is there a way a sub can look down a colum of names and that hav
several blank cells between them and when it comes to one that has bee
specified delete the row it is in and the rows between it and the nex
name to be evaluated.

I don't know where to begin on this one I have tried taking other cod
i have but can't get it delete the blank ones between entries.



regard
 
One way:-

Sub DelRows()

Dim LastRow As Long
Dim nextname As Long
Dim RngDel As Range

Application.ScreenUpdating = False

With ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))
End With

findme = InputBox("What name am I looking for??")

With Rng
Set found = .Find(what:=findme, LookIn:=xlValues)
If Not found Is Nothing Then
nextname = found.End(xlDown).Row
Set RngDel = ActiveSheet.Range(found, Cells(nextname - 1, "A"))
RngDel.EntireRow.Delete
End If
End With

Application.ScreenUpdating = True

End Sub
 
Should have said - Assumes data is in Col A

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Ken Wright said:
One way:-

Sub DelRows()

Dim LastRow As Long
Dim nextname As Long
Dim RngDel As Range

Application.ScreenUpdating = False

With ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))
End With

findme = InputBox("What name am I looking for??")

With Rng
Set found = .Find(what:=findme, LookIn:=xlValues)
If Not found Is Nothing Then
nextname = found.End(xlDown).Row
Set RngDel = ActiveSheet.Range(found, Cells(nextname - 1, "A"))
RngDel.EntireRow.Delete
End If
End With

Application.ScreenUpdating = True

End Sub
Click to expand...
 
do you mean you have multiple instances of a single findme value or you have
several values that you want to delete when found?
 
i think i figured it out, i copy pasted the part that actually finds the
names and changed the message box to = "TEXT" with the stuff that needs
to go. I pasted about a dozed of these and put the appropriate text.
still works fast to. Thank you for your help
 
Back
Top