Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
more row deleting
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
more row deleting
can i make several findme = "text to delete upon finding"
statments -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
more row deleting
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the text without deleting the formula | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |