Skip a step in the Macro if a range has no data

**1-Ton** · 07-31-2013, 02:33 PM

Good afternoon,

This is my first post here, but I have relied upon this forum for solutions to several other problems. So, gratitude is due for your indirect help on prior issues. Now hopefully you can help me directly with my current issue.

I have a spreasheet and macro which takes bulk data, filters it and pastes it into different tabs based on category, and then subtotals that data before copying and pasting all of the subtotals onto another sheet. The problem I am having is that occasionally there is no data within a certain category, so the correponding sheet will be blank (except for headers). When the macro runs, I get a "run-time error '1004'" when it attempts to sub-total the data on the blank sheet. Is there a way to skip this step of the macro if there is no data within a certain sheet/range?

If it helps, here is the portion of VBA language where it hangs-up:

Please Login or Register  to view this content.

**codeslizer** · 07-31-2013, 05:05 PM

First of all, A very warm welcome to the Forum, buddy!

And regarding your request.. you could try putting a check like the following. I use COUNTA() of WorksheetFunction to generally check the validation of a range.

Please Login or Register  to view this content.

**1-Ton** · 08-01-2013, 12:18 PM

That doesn't do the trick. Could it be because there is a header?

ps. Thanks for the warm welcome! I appreciate your help on this.

**1-Ton** · 08-05-2013, 02:43 PM

I still get the same error code with this. I'm not sure, but maybe because there is a header? Incidentally, I found a solution from another thread that is working. But, I'm not sure if it is the best. Let me know what you think:

On Error Resume Next
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
On Error GoTo 0