VBA code to only use Special Cells IF extra rows exist

[NatetheGreat]

HI All,
I've got a spreadsheet that via a Macro Button and some basic code I have written, will copy data into my spreadsheet table from another spreadsheet (via chdir ) . The data pasted in will have a few columns next to it, which have a series of formulas. My question is about the following part of my code

Range("stats[Time]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
'Chops Extra Rows Off Bottom Table

this is supposed to cut out any excess rows from the table, which are left over when a smaller dataset is copied into the table leaving blank rows at the bottom (and throwing off my formulas).

This works great when there are indeed extra rows, but when I'm copying in a LARGER dataset (i.e. more rows) into my table, the VBA doesn't know what to do and just fails here with a DEBUG, and doesn't execute some additional procedures I have afterwards.

Is there any way to change the above code so that it only executes IF there are indeed extra rows?

 

[AlphaFrog]

This will delete the blank rows or ignore it if errors.

[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
Range("stats[Time]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0

 

[NatetheGreat]

Hey AlphaFrog,
Thanks for the tip! Actually my boss just showed me the On Error Resume Next function, but I put it at the top of the sheet with no On Error Go to side.

Can you let me know if I incorporate your style of suggestion (below) does it matter that I have code both before and after this section? Just not understanding how the On Error GoTo 0 command works ? And also what the difference between what you wrote and what I have below will be ?


Thanks !

On Error Resume Next
Range("stats[Time]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
On Error GoTo 0

This will delete the blank rows or ignore it if errors.

[COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
Range("stats[Time]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
[COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0

 

[Rick Rothstein]

Hey AlphaFrog,
Thanks for the tip! Actually my boss just showed me the On Error Resume Next function, but I put it at the top of the sheet with no On Error Go to side.

Can you let me know if I incorporate your style of suggestion (below) does it matter that I have code both before and after this section? Just not understanding how the On Error GoTo 0 command works ? And also what the difference between what you wrote and what I have below will be ?

Thanks !

On Error Resume Next
Range("stats[Time]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
On Error GoTo 0

You should put the On Error Resume Next right before the code line(s) you are using it for and turn it off (that is what On Error GoTo 0 does) right after. Why? Because that On Error statement will hide all errors after it (up to when you turn it off), including errors you may not know you have in your code... those you do not want hidden from you because it would allow your program to continue executing with faulty information. For example, let's say you had this line of code in your program...

Var1 = Var2 / Var3

and, unbeknownst to you, Var3 was equal to 0 for some reason at that point... your program should warn you of the division by zero, but with On Error Resume Next active, it won't, so your program would continue executing using a faulty value for Var1 and you would not have any clue as to that fact.

 

[NatetheGreat]

Rick,

That's what I assumed. I don't want that to be the case so I will limit it just to this portion of the code. Thanks a lot. Everyone on this forum is fantastic !