Excel macro Help?

Question

i'm trying to run a macro that starts on a cell that is selected. failed example:

Sub TESTING()

Selection = currentCell

Do Until currentCell = ""

If currentCell < 0 Then
currentCell.EntireRow.Delete
End If

Set nextCell = currentCell.Offset(1, 0)
Set currentCell = nextCell
Loop

End Sub

Answer 1

You've got several things you need to do differently.

Sub TESTING()

Dim MyRow As Long
Dim MyColumn As Integer
Dim LastRow As Long
Dim x As Long

MyRow = ActiveCell.Row
MyColumn = ActiveCell.Column
LastRow = ActiveSheet.UsedRange. _
Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1

Application.ScreenUpdating = False

For x = LastRow To MyRow Step -1
If Cells(x, MyColumn).Value < 0 Then
Rows(x).Delete
End If
Next x

End Sub

1.) As the first person stated when you delete rows you start at the bottom and work your way up because Excel will throw your numbers off otherwise. The code I gave you does that. It starts at the bottom of the used range. Then notice that I used the:

Step -1

That forces it to evaluate from the bottom to the top by decreasing x by 1 each time the For...Next command loops.

2.) You don't use Set in the manner you were trying to use it. You use the Set command to declare what an object equals. To use the set command you'd need to declare something like:

Dim currentCell As Range

Set currentCell = ActiveCell

Notice that I first declared the variable as a Range object then I set the variable to be the active cell range. Notice I said range and not value.

3.) Turn the ScreenUpdating off except for if you need to get some input from a user with a messagebox, inputbox, or form. Then temporarily turn the ScreenUpdating back on. This makes your macro run quicker.

4.) Notice the use UsedRange. The UsedRange consists of the area of cells that contains all of the data you've entered into that worksheet. So there's no reason to evaluate to row 65,000 and something. And there's no reason to try getting it to end by telling the macro to end when it reaches the first blank cell in that column in order to find the last row in the UsedRange.

5.) It's a good idea to avoid moving the active cell and to avoid selecting cells to get the macro to perform commands unless absolutely necessary. This helps make the macro run faster and it helps avoid problems you could create by doing so also.

6.) Notice the data types I used for the declared variables. Data type is very important. You want to use the data type that uses the least amount of memory but accomplishes the job the way you want. The data type is declared after the "As". Notice for the Row variables that I declared them as Long and for the Column variable I declared it as an Integer. Both data types are numbers. The Integer data type takes up less memory than the Long data type. However, an Integer data type only goes up to about the number 30,000 or so. Excel has about 65,000 rows so the limits of that data type wouldn't allow the macro to possibly go to the last row in the column if needed. Since there are only 256 rows in Excel and the Integer data type takes up less space, the Integer data type is a better fit for identifying the Column.



I'm just trying to give you some good advice that I never got when I started writing in VBA. I'd recommend getting John Walkenbach's book:

Excel 2003 Power Programming with VBA

It will help you understand VBA a lot better. It does a good job of starting out with the basics and making sure you get the fundamentals down good. I wish I would have had a book like it when I started programming in VBA. It would have saved a lot of time and frustration.

One chapter you might really want to pay attention to in his book is Chapter 11. It deals a lot with working on cell ranges. It also talks about using arrays to evaluate the data instead of looping through the range and changing the cell values. Using arrays in this fashion can increase the speed of your macro by about 25 to 100 times.

Answer 2

Try:

Sub Testing()
For i = ActiveCell.Row To 65536
If Cells(i, ActiveCell.Column).Value = "" Then
Exit Sub
Else
Rows(i).EntireRow.Delete
i = i + 1
End If
Next i
End Sub

This isn't the best syntaxically because when deleting rows you should work from the bottom to the top because of how excel handles a cell being deleted, but this should get the job done. Another site you may want to check out is www.mrexcel.com