Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
I'm looking for code that looks in one specific column for
a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Try this
It will look for "Find" in the A column in Worksheets("Sheet1") Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 1 Step -1 If .Cells(r, "A").Value = "Find" Then .Rows(r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Pure genius! Now, can you explain it to the humans?
-----Original Message----- Try this It will look for "Find" in the A column in Worksheets ("Sheet1") Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 1 Step -1 If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
One more question: How can I substitute the active
worksheet name for "Sheet 1" (the worksheet name will be different each time)? TIA -----Original Message----- Try this It will look for "Find" in the A column in Worksheets ("Sheet1") Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 1 Step -1 If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Hi
For r = .UsedRange.Rows.Count To 1 Step -1 This will find the last row in the UsedRange and will loop from the bottem up to row 1 r = the row number in the loop We use Cells(row,column) to see if the cell in column A have the text "Find" in it and delete the row if the text is "Find" If .Cells(r, "A").Value = "Find" Then .Rows(r).Delete -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... Pure genius! Now, can you explain it to the humans? -----Original Message----- Try this It will look for "Find" in the A column in Worksheets ("Sheet1") Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 1 Step -1 If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Use
With ActiveSheet Instead of With Worksheets("Sheet1") -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... One more question: How can I substitute the active worksheet name for "Sheet 1" (the worksheet name will be different each time)? TIA -----Original Message----- Try this It will look for "Find" in the A column in Worksheets ("Sheet1") Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 1 Step -1 If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
That's the ticket, thanks again.
-----Original Message----- Use With ActiveSheet Instead of With Worksheets("Sheet1") -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... One more question: How can I substitute the active worksheet name for "Sheet 1" (the worksheet name will be different each time)? TIA -----Original Message----- Try this It will look for "Find" in the A column in Worksheets ("Sheet1") Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 1 Step -1 If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Just another couple of ways (I think the 2nd was Chip Pearson's):-
Sub DelRows() ans = InputBox("What string do you want rows to be deleted if they contain it?") Application.ScreenUpdating = False lastrow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Set Rng = Range(Cells(1, "A"), Cells(lastrow, "A")) With Rng .AutoFilter .AutoFilter Field:=1, Criteria1:=ans .SpecialCells(xlCellTypeVisible).EntireRow.Delete End With Application.ScreenUpdating = True End Sub ----------------------------------------------------------- Sub DelRowsIf() Dim RowNdx As Long Dim LastRow As Long Dim ans As String ans = InputBox("What string do you want rows to be deleted if it contains it?") LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If StrComp(Cells(RowNdx, "A"), ans, vbBinaryCompare) = 0 Then Cells(RowNdx, "A").EntireRow.Delete End If Next RowNdx End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Newsgroups - Where you really can get a free lunch!! ---------------------------------------------------------------------------- "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Can Ron de Bruin's code be modified to hide a row instead of deleting it?
If so, how? TIA Muzzy "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all rows with designated word
Use this line Muzzy
If .Cells(r, "A").Value = "Find" Then .Rows(r).Hidden = True -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Muzzy" wrote in message ... Can Ron de Bruin's code be modified to hide a row instead of deleting it? If so, how? TIA Muzzy "PHILLY" wrote in message ... I'm looking for code that looks in one specific column for a certain word ("delete") and deletes all those rows in which it appears. I'm trying to use "Find" so that I can delete them all at once rather than have it cycle through each row. I'm sure there is a better way......... Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
only count designated rows | Excel Discussion (Misc queries) | |||
How to substitute whole word with different designated letters? | Excel Worksheet Functions | |||
Fitting text in designated columns/rows | Excel Discussion (Misc queries) | |||
how to add word to the front of every word in all rows automatica. | Excel Discussion (Misc queries) | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions |