VB macro in Excel?

Question

I have an excel spreadsheet. A1:B500 has data (A=text; B=values)..with many blank cells.

How can I write an excel function with a macro to delete all blank rows..with one click?

Answer 1

This will delete all rows where column A & column B are blank. To assign to a button, go to View, Toolbars, Forms. Click on the buttonimage and click on the spreadsheet. You will be prompted to assign the button to a macro.

Sub DeleteCells()
Range("A1").Activate
Do Until ActiveCell.Row = 500
If ActiveCell.Value = "" And ActiveCell. Offset(0, 1).Value = "" Then
ActiveCell.EntireRow. Delete
End If
ActiveCell.Offset(1, 0).Activate
Loop


End Sub

Answer 2

I wouldn't use the second answer. The second answer could mess your values up. This would delete all of the blanks cells and shift them up. So for instance if you had three blank cells in column A and five blank cells in column B, then it would shift some of the cells in column B up farther and some of your values would no longer be with the matching data in column A.

The first macro would work correctly, but as I've learned more about creating macros, I try avoiding using the ActiveCell to cycle through the cells. It takes longer and it can cause more problems if done incorrectly. Most people start out in that manner because they look at the code that the macro recorder makes or they see some of the basic VB coding. Here's how I would do it:

Sub DeleteBlankRows

Application.ScreenUpdating = False

For x = 1 To 500

If x.Text = "" And x.Offset(0, 1).Text = "" Then
x.EntireRow.Delete
End If

Next x

End Sub


If you want it to do more than 500 rows, just change the number after "To" to a different number than 500. You'll also notice the line that I added that says:

Application.ScreenUpdating = False

This turns the screen updating off so that the screen doesn't update until the macro is done. That makes the macro run shorter and keeps the screen from flashing every time one of those 500 rows is checked. If the screen updating is left on, that screen has to update 500 times or at the very least the number of times that a row is deleted. Each time the screen updates that a little longer that it takes for that macro to run.

One note, this is a sub routine and not a function. Functions can only return values. They cannot perform actions like a sub routine does.

Answer 3

The previous answer will probably work, but this will be faster if you want to delete all of the blank cells in the worksheet (not restricted to a particular range):

Public Sub DeleteBlankRows()
Selection.SpecialCells (xlCellTypeBlanks).Delete
End Sub

You could also do this without a macro. Press F5 and then choose Special. Now, tell it to select the Blanks. Right click and then choose Delete and tell it to shift the cells up.

Tim
http://www.tvmcalcs.com