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

Simple VB Loop

P: 13
I am trying to create a do loop that will delete rows when the value in a column is 'Sale' or 'Distribution' and I need it to run until it reaches the bottom of the spreadsheet (incorporating blank cells along the way). Once this is complete, the data then needs to be output into a need tab.

I have tried and tried, but excel just freezes. It might be my coding (probably). I know this is minor, but help is needed quickly.

Many thanks
Nov 13 '06 #1
Share this Question
Share on Google+
7 Replies


100+
P: 1,646
I am trying to create a do loop that will delete rows when the value in a column is 'Sale' or 'Distribution' and I need it to run until it reaches the bottom of the spreadsheet (incorporating blank cells along the way). Once this is complete, the data then needs to be output into a need tab.

I have tried and tried, but excel just freezes. It might be my coding (probably). I know this is minor, but help is needed quickly.

Many thanks
Sound like you have an endless loop
Would you please post your code as that will be the fastest way to a solution
Thanks
Nov 13 '06 #2

P: 13
Sound like you have an endless loop
Would you please post your code as that will be the fastest way to a solution
Thanks
Code is as follows (so far):

Sub GetCache()

Sheets("PurchasesSales").Select
Range("A:K").Select
Selection.Copy

Sheets("Cache").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Cache").Select
Range("B2:B10000").Select
Do Until ActiveCell.Value = 0
If ActiveCell.Value = "Sale" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ElseIf ActiveCell.Value = "Purchase" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ElseIf ActiveCell.Value = "Distribution" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ElseIf ActiveCell.Value = " " Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 1).Select
End If
Loop

End Sub
Nov 13 '06 #3

100+
P: 1,646
Code is as follows (so far):

Sub GetCache()

Sheets("PurchasesSales").Select
Range("A:K").Select
Selection.Copy

Sheets("Cache").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Cache").Select
Range("B2:B10000").Select
Do Until ActiveCell.Value = 0
If ActiveCell.Value = "Sale" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ElseIf ActiveCell.Value = "Purchase" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ElseIf ActiveCell.Value = "Distribution" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ElseIf ActiveCell.Value = " " Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 1).Select
End If
Loop

End Sub
Hi. Thanks for the code.
You are running a risk of an endless loop by looking for the condition 0
Do Until ActiveCell.Value = 0

Also this might be easier to read, debug

Select Case ActiveCell.Value
Case "Sale", "Purchase", "Distribution"
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp

Case " "
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 1).Select
End Select

What exactly do you intend to do with that last statement;
ActiveCell.Offset(1, 1).Select
Nov 13 '06 #4

Expert 5K+
P: 8,434
You are running a risk of an endless loop by looking for the condition 0
Do Until ActiveCell.Value = 0
Perhaps as a first cut, this could be changed to watch for null?

Also, is it in fact locked, or could it simply be taking longer than expected? It might be a good idea to insert a DoEvents just before the Loop statement.
Nov 13 '06 #5

P: 13
Hi. Thanks for the code.
You are running a risk of an endless loop by looking for the condition 0
Do Until ActiveCell.Value = 0

Also this might be easier to read, debug

Select Case ActiveCell.Value
Case "Sale", "Purchase", "Distribution"
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp

Case " "
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 1).Select
End Select

What exactly do you intend to do with that last statement;
ActiveCell.Offset(1, 1).Select
I have tried your code, but it results in the same problem, that my Excel freezes and I have to restart the application.

All I want is for the lines in the spreadsheet with Distribution, Sale, Purchase in column B to be deleted. Is anyone able to write a quick piece of code that will do this process? Ive tried till im blue!

The ActiveCell.Offset statement was to move the curosr down to the next line.

HELP!
Nov 14 '06 #6

100+
P: 1,646
I have tried your code, but it results in the same problem, that my Excel freezes and I have to restart the application.

All I want is for the lines in the spreadsheet with Distribution, Sale, Purchase in column B to be deleted. Is anyone able to write a quick piece of code that will do this process? Ive tried till im blue!

The ActiveCell.Offset statement was to move the curosr down to the next line.

HELP!
Yes it will move it down one row. It will also move it to the right by one column.
The code you are using works. I just tested it. There are two problems. First you are only exiting the loop if you encounter 0. This works if there is one and locks you into a permanent loop if there isn't one.
Second the Offset statement moves you to a new column. Try the code without the offset. Put a counter in the code as a safety exit. You are looking through 10,000 rows so;

Dim intCounter As Integer

'and inside your loop
intCounter = intCouter + 1
If intCounter = 10000 Then
Exit Sub
End If
Nov 14 '06 #7

Expert 5K+
P: 8,434
Another important point. If you have a DoEvents in there, you won't have to restart the application, even if you do get stuck in a loop - you can just interrupt the code. DoEvents allows Windows to notice other things, like the Break key (or mouse-clicks).
Nov 14 '06 #8

Post your reply

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