How to exit a Sub if a range of cells isn't filled?

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I have a macro which updates Pivot tables in my spreadsheet.

However, if a range of cells in a specific tab, let's say Sheet2, is blank, then I don't want to run the macro.

@Fluff helped me out with the code below, which checks if two cells that in Sheet6 are blank, then exits the Sub, if they are. And this code is really useful!

However, I need to do something similar for a separate tab, so if cells AM15:AM66 were blank, then the Sub would stop running.

Any thoughts on how to add this to the bottom of this code would be greatly appreciated....

VBA Code:
Sheet6.Visible = True

'check if cells B3 and C3 in Sheet 6 have data. 

Sheet6.Activate

If Range("B3") = "" And Range("C3") = "" Then

MsgBox "Check that Tab 6 has been populated"

Exit Sub

End If
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

Sheets("Sheet2").Select
If WorksheetFunction.CountA(Cells) = 0 Then Exit Sub
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi @AlanY

Thanks for the prompt response.

Apologies - I should have clarified at the outset that I only want to exit the Sub IF there is no data in cells AM15:AM66 in Sheet2.

Do you know how to modify the code so that it checks that column only?

Thanks in advance
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
would try this instead

If IsEmpty(Sheets(" Sheet2 ").Range(" AM15:AM66 ").Value) = True Then Exit Sub
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Alan

Apologies for the delay in responding.

Thanks for posting the possible solutions above.

I tried both of them, however, and they didn't give the expected result?

To recap, I want to check if certain cells in Sheet2 are populated (AM16:AM66).

If they are, then I'd like to carry on with the rest of the actions in the Sub.

If they're not, then I'd like to exit the Sub.

To test the code, I created a new workbook with two sheets: Sheet1 and Sheet2.

I then populated cells A1, A2, A3 and 4, with the values 1, 2, 3 and 4, respectively.

If AM15:AM66 were populated, then I'd want to copy A1:A4 and paste the data in E1.

If not, then exit the Sub with a message.

I intentionally left AM15:AM66 blank and tested both sets of code below - can you please advise if there's reason why you think neither Subs would work?

In both instances, the Message Box should be triggered, as AM15:AM66 are blank, but that doesn't happen?

Please note that not every cell in the AM15:AM66 needs to be filled, but for the rest of the Subs to run, at least some of the cells in that range would need to be populated.

Please let me know if you'd like me to clarify anything?

Sub 1

VBA Code:
Sub Test()

Sheets("Sheet2").Select
If WorksheetFunction.CountA(Cells) = 0 Then Exit Sub

MsgBox "Sheet 2 is blank"

Sheet1.Activate
Range("a1", Range("a1").End(xlDown)).Select

Selection.Copy

Range("E1").PasteSpecial xlPasteAll

End Sub


Sub 2 - this Sub gives a 'Compile error: End if without block if' if you remove the apostrophe before the 'End If' statement?

VBA Code:
Sub TestII()

Sheets("Sheet2").Select
If IsEmpty(Sheets(" Sheet2 ").Range(" AM15:AM66 ").Value) = True Then Exit Sub

MsgBox "There's no data in cells AM15:AM66"

'End If

Sheet1.Activate
Range("a1", Range("a1").End(xlDown)).Select

Selection.Copy

Range("E1").PasteSpecial xlPasteAll

End Sub
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Sub test() works as intended for me.

as for Sub testII(), note that Sheets(" Sheet2 ") is not the same as Sheets("Sheet2"), similar to Range(" AM15:AM66 ").
if you change that by removed the extra spaces to

If IsEmpty(Sheets("Sheet2").Range("AM15:AM66").Value) = True Then Exit Sub

that should work
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top