more row deleting
 

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

--
Message posted from http://www.ExcelForum.com

more row deleting
 

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


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

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



"ksnapp " wrote in message
...
hi is there a way a sub can look down a colum of names and that have
several blank cells between them and when it comes to one that has been
specified delete the row it is in and the rows between it and the next
name to be evaluated.

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



regards


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 04/03/2004

more row deleting
 

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" wrote in message
...
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


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

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



"ksnapp " wrote in message
...
hi is there a way a sub can look down a colum of names and that have
several blank cells between them and when it comes to one that has been
specified delete the row it is in and the rows between it and the next
name to be evaluated.

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



regards


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 04/03/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 04/03/2004

more row deleting
 

works great and easy to understand thank yo

--
Message posted from http://www.ExcelForum.com

more row deleting
 

can i make several findme = "text to delete upon finding"
statments

--
Message posted from http://www.ExcelForum.com

more row deleting
 

do you mean you have multiple instances of a single findme value or you have
several values that you want to delete when found?

--
Regards,
Tom Ogilvy

"ksnapp " wrote in message
...
can i make several findme = "text to delete upon finding"
statments?


---
Message posted from http://www.ExcelForum.com/

more row deleting
 

several values to find and delete

thank yo

--
Message posted from http://www.ExcelForum.com

more row deleting
 

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


---
Message posted from http://www.ExcelForum.com/