Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that contain a certain value in column A
I have a spreadsheet sorted by column A. I need a procedure that will find
every cell with a specific value (they will all be grouped together because of the sort) in column A and delete every row (ever how many it is) that has that value in column A. Then I need the remaining data shifted up. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that contain a certain value in column A
Hi,
Right click your sheet tab, view code and paste the code below in and run it. It currently looks for the number 3 in column A so change to suit. If your looking for a text value it must be in quotes "Myvalue" Sub delete_Me2() Dim copyrange As Range lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If c.Value = 3 Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "Bishop" wrote: I have a spreadsheet sorted by column A. I need a procedure that will find every cell with a specific value (they will all be grouped together because of the sort) in column A and delete every row (ever how many it is) that has that value in column A. Then I need the remaining data shifted up. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that contain a certain value in column A
Suppose we want to remove "misery":
Sub StayHappy() Dim n As Long Dim i As Long n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, "A").Value = "misery" Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200848 "Bishop" wrote: I have a spreadsheet sorted by column A. I need a procedure that will find every cell with a specific value (they will all be grouped together because of the sort) in column A and delete every row (ever how many it is) that has that value in column A. Then I need the remaining data shifted up. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows that contain a certain value in column A
How about removing "misery" something like this non-looping way instead?
Sub StayHappy() Dim Word As String Word = "misery" Columns("A").AutoFilter Criteria1:=Word, Field:=1, VisibleDropDown:=False Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).Offset( _ Abs(Range("A1").Value < Word)).EntireRow.Delete ActiveSheet.AutoFilterMode = False End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Suppose we want to remove "misery": Sub StayHappy() Dim n As Long Dim i As Long n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, "A").Value = "misery" Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200848 "Bishop" wrote: I have a spreadsheet sorted by column A. I need a procedure that will find every cell with a specific value (they will all be grouped together because of the sort) in column A and delete every row (ever how many it is) that has that value in column A. Then I need the remaining data shifted up. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows meeting certain criteria in a particular column | Excel Discussion (Misc queries) | |||
Deleting Rows using a range in Column A | Excel Programming | |||
Deleting rows with no value in column A | Excel Programming | |||
Deleting rows with no value in column A | Excel Programming | |||
Deleting rows based upon the value in column D | Excel Programming |