# Delete rows with macros

Hewlett-packard / Hp elitebook 8540w
August 3, 2010 at 17:46:23
Specs: Microsoft Windows XP Professional, 2.66 GHz / 3061 MB
 I have data listed in over 1,000 rows and in column A of those rows are listed a "v" or "r" or blank. In column D there is a 6 digit number and there is other data in all of the other columns as well, like so:---A----B-----C----D---------E----F1.-V----10----7----691352----G----15855662.-R----10----7----691399----G----15888563. -----10----7----691352----G----1585566In my example above I need to find all "v" values in column A and then delete all rows that have the value lsited in column D. In my example I would find a "v" in column A and the delete rows 1 and 3 since they share the same value. Any help would do. This is the code I started working with:Dim LSearchRow As IntegerLSearchRow = 1While Len(Range("D" & CStr(LSearchRow)).Value) > 0 If Range("A" & CStr(LSearchRow)).Value = "v" ThenNot sure where to go from here. Thanks for your help

See More: Delete rows with macros

#1
August 3, 2010 at 18:36:28
 A question:In your example, we are looking to delete all rows with a 691352 in Column D, because 691352 was found in a row with a V in Column A.Will 691352 be found farther down in the column D, after another V? In other words is this a possibility? Look in Row 8.``` A B C D E F 1 V 10 7 691352 G 1585566 2 R 10 7 691399 G 1588856 3 10 7 691352 G 1585566 4 R 10 7 691399 G 1588856 5 10 7 691399 G 1585566 6 V 10 7 691500 G 1585566 7 R 10 7 691399 G 1588856 8 10 7 691352 G 1585566 9 V 10 7 691506 G 1585566 10 R 10 7 691399 G 1588856 11 10 7 691506 G 1585566```

Report •

#2
August 3, 2010 at 20:59:17
 yes it is.

Report •

#3
August 3, 2010 at 21:51:10
 Give this a shot.```Option Explicit Sub DeleteBasedOnV() Dim lastD_Row, nxtRw, delRw As Integer Dim myDelVal As String 'Start of each loop nxtV: 'Find last row with data in Column D lastD_Row = Range("D" & Rows.Count).End(xlUp).Row 'Loop through Column A looking for V For nxtRw = 1 To lastD_Row If Range("A" & nxtRw) = "V" Then 'If found, set variable = to value in Column D myDelVal = Range("D" & nxtRw) 'Loop backwards through Column D, deleteing rows For delRw = lastD_Row To 1 Step -1 If Range("D" & delRw) = myDelVal Then _ Range("D" & delRw).EntireRow.Delete Next 'Start again GoTo nxtV End If Next End Sub ```Please see Response 11 of this tread to understand why we have to loop backwards through a Column to delete Rows.

Report •

Related Solutions