Excel VBA - If column does not have set value, then end private sub

Question

I'm having an issue with my macro. It will filter on X criteria in a column, copy all that data, then paste into new sheet and save. If X criteria does not exist in the column, it will still go on to save as a new sheet and the sheet will be blank and have a huge file size. How can I get VBA to check if X criteria exists in Y column, and if so, continue the macro. Otherwise, skip the entire private sub.

This is the section I'm having issues with. I'm thinking maybe using an If-Else statement might work but I'm not sure how to incorporate that properly....like If Column 21 has "X" then Continue. Else, skip entire sub.

Private Sub SaveExternalCopy(ByVal Affiliate As String)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

dt = Format(DateAdd("m", -1, Now), "mm.yyyy")
Dim mhoWb As Workbook
Set mhoWb = Workbooks("All_" & dt & " " & " Macro Enabled")
Dim mhoSheet As Worksheet
Set mhoSheet = Sheets("MHO")
    mhoSheet.Activate
    mhoSheet.UsedRange.AutoFilter field:=21, Criteria1:=Affiliate & " " & "Recon"
With mhoSheet
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End With

Also read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|243.4716 — SJR, May 08 '18 at 15:54
@SJR Thank you for that link. I've been trying to find ways to optimize the code I write my code. You pop up on most of my questions so thank you very much for all your help! — J Doe, May 08 '18 at 16:15

score 0 · Answer 1 · answered May 08 '18 at 16:01

0

You can check for the number of visible rows after the filter is applied:

mhoSheet.UsedRange.AutoFilter field:=21, Criteria1:=Affiliate & " " & "Recon"
If mhoSheet.UsedRange.rows.SpecialCells(xlCellTypeVisible).rows.count = 1 Then
    '--- none of the rows passed the filter, so just exit
    Exit Sub
End If

answered May 08 '18 at 16:01

PeterT

8,232
1
17
38

hmm..this works for the first time but I run the Private Sub 5 times over since there are 5 Affiliates. This code will work for the first affiliate since it has no data but when it comes to the other 4, it will exit sub even though it shouldn't since they have data. I tried doing rows.count = 0 but that didn't work but I figured if 0 rows are visible then that'd be the solution right? – J Doe May 08 '18 at 16:14
I'm assuming there will be a headed row, which is why I specified rows=1. You'll have to adjust this value to suit your situation. – PeterT May 08 '18 at 16:24

score 0 · Answer 2 · answered May 08 '18 at 16:33

I figured out a workaround - it isn't the prettiest and it probably isn't the most efficient but it works at least!

Sub ShowAllData()

On Error Resume Next
Worksheets("My_Health_Online").ShowAllData

End Sub

'

Private Sub SaveExternalCopy(ByVal Affiliate As String)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim rngFoundCell As Range

dt = Format(DateAdd("m", -1, Now), "mm.yyyy")
Dim mhoWb As Workbook
Set mhoWb = Workbooks("All" & dt & " " & "Macro Enabled")
Dim mhoSheet As Worksheet
Set mhoSheet = Sheets("MHO")
    mhoSheet.Activate
    ShowAllData
    Set rngFoundCell = Sheets("MHO").Range("U2:U100000").Find(What:=Affiliate & " " & "Recon")

If rngFoundCell Is Nothing Then
        Exit Sub
Else

After Else, I put the rest of my code that would run normally. This code looks at column U for X value. I had to use ShowAllData since I was running this code 5 times over, it was creating a weird situation where it didn't find any values in column U since they were hidden by the filter from the first few times the code ran.

Excel VBA - If column does not have set value, then end private sub

2 Answers2