By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,726 Members | 1,219 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,726 IT Pros & Developers. It's quick & easy.

VBA for Excel 03' need help with delete row macro

P: 10
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....deleting 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!

Expand|Select|Wrap|Line Numbers
  1. Sub Delete_Loop()
  3. Dim x As Integer
  5. Range("B1").Select
  7. If Range("B1").Value = "Contract Type" Then
  8.     Rows("1:1").Select
  9.     Selection.Delete Shift:=xlUp
  10.     Exit Sub
  12. x = 1
  14. Do
  16. If Range("B" & x).Value = "SMBS" Then
  17.     Exit Sub
  18. If Range("B" & x).Value = ActiveCell.Value <> Empty Then
  19.     Rows("x:x").Select
  20.     Selection.Delete Shift:=xlUp
  21. End If
  23. x = x + 1
  25. Loop While x < 50
  27. End Sub
Aug 6 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 112
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.
Expand|Select|Wrap|Line Numbers
  1. Sub Test()
  2.     Range("B1").Select
  3.     Dim x As Long
  4.     Do
  5.         If x > 50 Then Exit Sub
  6.         If ActiveCell.Value = "Contract Type" Then
  7.             ActiveCell.Rows.Delete
  8.             Exit Sub
  9.         Else
  10.             ActiveCell.Rows.Delete
  11.         End If
  12.         x = x + 1
  13.     Loop Until ActiveCell.Value = "SMBS"
  14. End Sub
Aug 6 '08 #2

Expert Mod 15k+
P: 31,754
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).
Aug 6 '08 #3

P: 10
Janders468...thank you very much for that. I added EntireRow (ActiveCell.Rows.EntireRow.Delete) 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?
Aug 6 '08 #4

Expert Mod 15k+
P: 31,754
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?
Aug 6 '08 #5

P: 10
Yes that makes sense...thank you.
Aug 6 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.