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----F
1.-V----10----7----691352----G----1585566
2.-R----10----7----691399----G----1588856
3. -----10----7----691352----G----1585566

In 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 Integer

LSearchRow = 1


While Len(Range("D" & CStr(LSearchRow)).Value) > 0

If Range("A" & CStr(LSearchRow)).Value = "v" Then

Not sure where to go from here.


Thanks for your help


See More: Delete rows with macros

Report •


#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


Ask Question