Deleting Variable Amount of Rows

A

alexdelwar

New Member
Joined
May 4, 2018
Messages
3
Hello,

Would truly appreciate if anyone can help me with this problem.

Original data:


Any help would be appreciated.
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Sorry. Messed up attaching photos. First time posting something...

Hello,

Would truly appreciate if anyone can help me with this problem.

Original data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]X
[/TD]
[TD]1321
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]42145[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123412[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]31231[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]32131[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64, align: right"]432[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]X[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]Y[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64, align: right"]123[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]X[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64, align: right"]123[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]X[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64, align: right"]1[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]241[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64, align: right"]23[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]124[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I need to delete rows with the letter Y in column A and any rows below until the next letter appears in column A. It doesn't matter what the actual next letter is. I'm looking for vba code as i have thousands of rows.

Result:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]X
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]1321[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]42145[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123412[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]X[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]X[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]X[/TD]

</tbody>
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]241[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"]124[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be appreciated.
 
Upvote 0
Slow method...

Rich (BB code): 
Sub DeleteY()
    Dim i As Long, lr As Long
    
    Application.ScreenUpdating = False
    
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = lr To 1 Step -1
        If UCase(Cells(i, "A")) = "Y" Then Range(Cells(i, "A"), Cells(i, "A").End(xlDown).Offset(-1)).EntireRow.Delete
    Next
    
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Fast method, though you may not notice the difference unless you have tens or hundreds of thousands of rows.

Code: 
Sub Del_Y_and_Following_Blanks()
  Dim a, b
  Dim nc As Long, i As Long, k As Long

  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("A1", Range("B" & Rows.Count).End(xlUp).Offset(1)).Value
  a(UBound(a), 1) = "@"
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) = "Y" Then
      b(i, 1) = 1
      k = k + 1
      Do Until a(i + 1, 1) <> ""
        i = i + 1
        k = k + 1
        b(i, 1) = 1
      Loop
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A1").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub
 
Upvote 0
MARK858 said:
Slow method...
Click to expand...
Mark, I don't know if it is possible with the OP's data, but if the following was possible, your code would delete rows that it shouldn't.


Book1
AB
1Y1
21
3Y1
4X1
5X1
6X1
7X1
Sheet2
 
Upvote 0
Peter_SSs said:
Mark, I don't know if it is possible with the OP's data, but if the following was possible, your code would delete rows that it shouldn't.
Click to expand...

I know but I based my reply on the data posted :biggrin:
 
Upvote 0
MARK858 said:
I know but I based my reply on the data posted :biggrin:
Click to expand...
Fair enough. I interpreted the any in the OP's comments to mean the possibility of none. At least the issue has been raised for the OP or other readers. :)

alexdelwar said:
.. delete rows with the letter Y in column A and any rows below until the next letter appears in column A.
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
Inserting a Pivot Table a few rows below a dynamic Range
Replies
2
Views
74
Yeft
Y
P
  • Solved
Delete Cell if 3rd character not equal to...
Replies
9
Views
247
Peter_SSs
Peter_SSs
S
  • Question Question
Compressing large amount of rows but adding totals?
Replies
6
Views
119
jkpieterse
jkpieterse
N
  • Solved
COUNTA Function not counting any cells
Replies
1
Views
113
nithchun
N
G
  • Solved
Improving range references to Table rows (dynamic instead of fixed)
Replies
5
Views
411
RoryA
RoryA

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top