delete sheet if a2 is blank and then continue MACRO

[cornfused1954]

thanks to some of the fine folks here, I have been able to put together a pretty hefty MACRO. I found out that some of the files I am importing may result in no data due to the manipulations I am doing. This causes a problem when I try to subtotal the data on each sheet. Is there a way to have the MACRO delete a sheet if cell a2 is blank and then continuw with the rest of the MACRO?

 

[Smitty]

Without seeing your code, maybe you can incorporate something like this:

Application.DisplayAlerts = False
 If LenB(ActiveSheet.Range("A2")) = 0 then ActiveSheet.Delete
Application.DisplayAlerts = True

Hope that helps,

Smitty

 

[prabby81]

Smitty,

One question, If we delete the sheet that is activated right now, won't we specify the sheet that we want to go to next?

Also, since we are disabling the alerts, if there is only one sheet, with A2 cell empty, what'll happen?

Just want to know out of curiosity. Haven't tried this...

Thanks!

 

[Smitty]

One question,

Actually, you've got two.

If we delete the sheet that is activated right now, won't we specify the sheet that we want to go to next?

The next activesheet will be the next to the right, unless you specifically activate a different one.

Also, since we are disabling the alerts, if there is only one sheet, with A2 cell empty, what'll happen?

Turning off alerts will only apply to deleting that one activesheet in the context that I posted it. All it does in this case is suppress the Excel message asking if you want to delete the sheet if there is data in it. If it's entirely blank, there will be no prompt anyway.

You might want to consider posting your code, so we can see what's you're trying to do.

Smitty

 

[prabby81]

Thanks Smitty. I've actually got one more question for you, or rather a confirmation from you!

If I am understanding the code correctly, what would happen is that it would check the active sheet's A2 cell. If it's blank, it would delete the sheet.

-> Now, it would go to the next sheet, that is one to the right. The same code would run again, and it would delete this sheet as well if A2 is blank. And it would run till all the sheets are checked and deleted if meeting the criteria ...

If I am right, then there should be another code in there to select the first sheet, or whichever sheet one wants to start checking from...

I am a beginner in VBA, and m sort of running through different codes in here, so am trying to learn as much as I can...

Thanks for your help buddy !

 

[Norie]

Prabby

I think it actually really depends on the original OPs code.

Smitty's example uses ActiveSheet but we don't know if that's how the OP's code is working.

ie are the using ActiveSheet or another method?

As to the display alerts question, if you try to delete a worksheet when there is only 1 worksheet you'll get a runtime error.

 

[prabby81]

Thanks Norie & Smitty ! Really appreciate the help you guys provide in here !

 

[Norie]

Prabby

Smitty's code won't do anything other than delete the sheet if the criteria is satisfied.

To know what would happen after that we would actually need to see the OP's code.

 

[cornfused1954]

Without seeing your code, maybe you can incorporate something like this:

Application.DisplayAlerts = False
 If LenB(ActiveSheet.Range("A2")) = 0 then ActiveSheet.Delete
Application.DisplayAlerts = True

Hope that helps,

Smitty

thanks, Smitty, it worked like a charm. all of the comments had me worried but it was SMOOOOOOOooooooooOOOOOOTH.

 

[Smitty]

If you wanted to cycle through each sheet you'd need something like this:

Dim ws as Worksheet
  For Each ws in ActiveWorkbook.WorkSheets
    Application.DisplayAlerts = False 
      If LenB(ActiveSheet.Range("A2")) = 0 then ActiveSheet.Delete 
    Application.DisplayAlerts = True
  Next ws
End Sub

Keping Norie's comments in mind about what you're trying to do and the error factor, you really should post your code.

Smitty