Need help refining my excel macro for deleting blank rows or performing another action

Question

Basically what I'm trying to accomplish is to search the document for blank rows and delete them, if any. This works great if there are blank rows to delete; however, if there are no blank rows, the macro ends with an error. I'd be eternally grateful if someone could advise me how to make this into an "if blank rows then this, if none then that"

Sheets ("xml") .Select
Cells.Select
Selection.SpecialCells(x1CellTypeBlanks).Select
Selection.EntireRow.Delete

Enter my second macro (this part works fine)

Regards

Answer 1

Let me point you to the canonical:

How to avoid using Select/Activate in Excel VBA macros

So you can start to understand why your current code fails or performs undesired operation. What happens when there are no blank cells in your selection? You'll get an error. Why?

Because in that circumstance, Selection.SpecialCells(xlCellTypeBlanks) evaluates to Nothing. (You can verify this using some debug statements) And because Nothing does not have any properties or methods, you'll get an error, because you're really saying:

Nothing.Select

Which is a null program, does not grok, does not compute, etc.

So, you need to test for nothingness with something like this:

Sheets("xml").Select
Cells.Select
If Not Selection.SpecialCells(x1CellTypeBlanks) Is Nothing Then 
    Selection.SpecialCells(x1CellTypeBlanks).EntireRow.Delete
End If

I still suggest avoiding Select at all costs (it is superfluous about 99% of the time and makes for sloppy code which is difficult to debug and maintain).

So you could do something more complete following that line of thought:

Dim blankCells as Range  '## Use a range variable.

'## Assign to your variable:
Set blankCells = Sheets("xml").Cells.SpecialCells(xlCellTypeBlanks)

'## check for nothingness, delete if needed:
If Not blankCells Is Nothing then blankCells.EntireRow.Delete

Follow-up from comments

So in VBA we are able to declare variables which represent objects or data/values, much like a maths variable in an equation.

A Range is a type of object part of the Excel object model, which consists of the Workbook/Worksheets/Cells/Ranges/etc. (far more than I could hope to convey to you, here)

http://msdn.microsoft.com/en-us/library/office/ff846392(v=office.14).aspx

A good example of why to use variables might be here if you scroll down to the "Why Use Variables" section.

http://www.ozgrid.com/VBA/variables.htm

This is of course very simple... but the reader's digest version is that variables allow us to repeatedly refer to the same object (or value for sipmle data types) without explicitly referring to it each time.

THen there is the handy side-effect that the code bcomes more easy to read, maintain and debug, when we use variables instead of absolute references:

Dim rng as Range

Set rng = Sheets(1).Range("A1:Q543").Resize(Application.WorksheetFunction.CountA(Sheets(1).Range("A:A"),))

Imagine that fairly (but not ridiculously) complicated range construct. If you needed to refer to that range more than once in your code, it would be silly not to assign it to a variable, if for no other reason than to save your own sanity from typing (and possibly mistyping a part of it). It is also easy to maintain, since you need only modify the one assignment statement and all subsequent references to rng would reflect that change.