VBA for Excel 03' need help with delete row macro

[N2Deep]

I'm new at trying to write VBA, and have been working on this delete loop for a while, and could use some help.

I want the macro to start in cell B1, if it finds the string "Contract Type" in cell B1 I want it to delete that entire row and exit (Contract Type will only ever appear in cell B1).

If it doesn't find string "Contract Type" in above, I would like it to move down the B column one cell at a time....deletin g all rows with an empty B cell, until it comes to the string "SMBS" at which point I would like it to exit (SMBS will never be more than 46 rows down the sheet).

Here is what I have so far...thanks in advance for your help!

Sub Delete_Loop()
 
Dim x As Integer
 
Range("B1").Select
 
If Range("B1").Value = "Contract Type" Then
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Exit Sub
 
x = 1
 
Do
 
If Range("B" & x).Value = "SMBS" Then
    Exit Sub
If Range("B" & x).Value = ActiveCell.Value <> Empty Then
    Rows("x:x").Select
    Selection.Delete Shift:=xlUp
End If
 
x = x + 1
 
Loop While x < 50
 
End Sub
 
 

[janders468]

this isn't the most extensible solution it's fairly hardcoded, assumes that you want to use the ActiveSheet etc. You could work with it but this logic does what I believe you are looking for.

Sub Test()
    Range("B1").Select
    Dim x As Long
    Do
        If x > 50 Then Exit Sub
        If ActiveCell.Value = "Contract Type" Then
            ActiveCell.Rows.Delete
            Exit Sub
        Else
            ActiveCell.Rows.Delete
        End If
        x = x + 1
    Loop Until ActiveCell.Value = "SMBS"
End Sub
 

[NeoPa]

A couple of things to bear in mind here (referring to N2Deep's original code).

1. When deleting lines it is better to start from the bottom. Otherwise you have to remember NOT to increment the row number after a line is deleted. Think about it.
2. In your code at line #18, this is a continuation of the If statement from line #16. In other words it can never be run (as it follows a line Exit Sub).

Otherwise your code is not far off.

Perhaps look through it using the debugger, and see where it's not behaving as you'd expect it to (Debugging in VBA).

[N2Deep]

Janders468...th ank you very much for that. I added EntireRow (ActiveCell.Row s.EntireRow.Del ete) and it worked like a charm.

NeoPa...thank you for helping me understand. I see what you are saying about lines 18 & 16. I do also understand about starting from the bottom, but would you recommend that in this case where I sometimes I have up to 30k+ lines of data?

[NeoPa]

If all 30K lines needed to be searched, yes.

If you are still talking about a range of 50 lines at the top, then "the bottom" in this case means at line 50.

Does that clarify?

[N2Deep]

Yes that makes sense...thank you.