Error handler if no rows

I need to include code to handle errors that may occur, but I have been unsuccessful.
At two points, there could be no data in the list.
I need to stop and display an error msg box rather than having the user stop the code.

The first point in the code is when the data is pasted from Data sheet to Filtered_data sheet.
HEADERS_MIN_PACKAGE()
    'create criteria range
    Set rng = Sheets("Data").Range("a1").End(xlToRight).Offset(0, 2)
    rng.Value = frmData.lblRatedWeight.Caption
   
    rng.Offset(1, 0).Formula = "="">=""&" & "Details!A3"
    rng.Offset(1, 0).Calculate
   
    Sheets("Data").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Data").Range(rng, rng.Offset(1)), CopyToRange:=Range("Filtered_Data!A1"), Unique:=False
   
    Sheets("Data").Range(rng, rng.Offset(1)).Value = vbNullString

If there is no data for Filtered_Data sheet, I need a msg "There are no shipments meeting the min package weight"
_______________________________________________________________________________________________________________________

The second point in the code is the Filter () when there is nothing on the Report tab after -

 Selection.AutoFilter
rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value

Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

I need a msg box to say "There are no shipments meeting the min shipment weight."
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Euro5Author Commented:
Sorry, forgot to attach.
TEST-FINAL.xlsm
Roy CoxGroup Finance ManagerCommented:
SpecialCells will raise an error if there are no cells that meet the criteria

On Error Resume Next
Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0

Open in new window


It might be easier to use AutoFilter rather than AdvancedFilter if you only have one criteria. Then you can check the number of rows before copying.
Roy CoxGroup Finance ManagerCommented:
I'm not sure if this is what you want

    'trim zips
    With Worksheets("Filtered_Data").Range("A1")

        If .Rows.Count < 2 Then
            MsgBox "No data to work with"
            Exit Sub
        End If

        .CurrentRegion.Rows (1)

        For Each Item In astrHeaders
            Set f = .Find(Item, LookIn:=xlValues)
            If Not f Is Nothing Then
                Set mCol = Range(Cells(2, f.Column), Cells(Rows.Count, f.Column).End(xlUp))
                For Each c In mCol
                    If Len(c.Value) = 9 Then
                        c.Value = Left(c.Value, 5)
                    ElseIf Len(c.Value) = 8 Then c.Value = Left(c.Value, 4)
                    End If
                Next
            End If
        Next
    End With
End Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Euro5Author Commented:
Yes! It looks like this is working - but it doesn't stop the macros from running.
Can we get the error, then have it exit everything?
Roy CoxGroup Finance ManagerCommented:
Sorry can you explain what is working. Maybe you mean

Selection.AutoFilter
 rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value
 ; if an error occurs go to the end and give a message, then quit
On Error GoTo exit_proc
Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
' any other code
;
'code run successfully so finish here
Exit Sub
exit_proc:
MsgBox "No rows to delete", vbCritical, "Quitting"

Open in new window


When you have this solved I suggest that you have a tidy up of the code - remove all the empty modules and put some notes in about what the code is doing.

Also, yo don't need a separate module for each macro
Euro5Author Commented:
I tried it, but it didn't recognize that there were no rows and didn't stop the code.
Maybe I can try another way that will help.

I am attaching a sample, if you could enter it in here, maybe that would make it more clear.

Maybe if I explain, it will help you to see what I need.

What the code must do is
1. Take the data from DATA sheet, and filter on Rated Weight using >= the value on DETAILS sheet A3. This would represent the minimum weight that any package could be. In the workbook, that is 20. What if I change that to 30 and there are no packages >= 30? I need an error that would tell the user and stop the program from continuing.

2. After it takes the list of those packages and puts them on FILTERED_LIST it filters on Rated Weight using >= the value on DETAILS sheet B3. This would represent the minimum weight that any shipment could be. In the workbook, that is 150. What if I change that to 6000 and there are no shipments that >=6000?  I need an error that would tell the user and stop the program from continuing.

I hope I explained that well enough. I am really desperate to complete this and am down to the final few issues.
Please help?? :)

I will certainly clean up the code once I get it running - thanks so much for the suggestions!
test-for-no-rows-for-analysis.xlsm
Roy CoxGroup Finance ManagerCommented:
Which macro is it?
Euro5Author Commented:
The first is HEADERS_MIN_PACKAGE()
The second is Filter()
Roy CoxGroup Finance ManagerCommented:
With HEADERS_MIN_PACKAGE() the code only has one criteria. There is no simple way that I can think of to test how many rows if any will be copied, unlike with AutoFilter. Your code does not clear previous copied data, is that correct? nI would actually use AutoFilter, test the results and quit if there are no rows to copy. Also with on criteria the code only copies that column.

There does not appear to be a macro called Filter()
Roy CoxGroup Finance ManagerCommented:
I've found TILTER. This throws an error message if no rows found

Sub FILTER()
    Application.ScreenUpdating = False

    Sheets("Report").Select
    ActiveSheet.Name = "Report"

    Dim i As Integer, rngData As Range
    Set rngData = Range("A1").CurrentRegion
    i = Application.WorksheetFunction.Match("Rated Weight", Range("A1:AZ1"), 0)

    Rows("1:1").Select
    'Selection.AutoFilter
    'rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value

    'Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    'If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

    Selection.AutoFilter
    rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value
    ' if an error occurs go to the end and give a message, then quit
    On Error GoTo exit_proc
    AutoFilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    ' any other code
    '
    'code run successfully so finish here
    Exit Sub
exit_proc:
    MsgBox "No rows to delete", vbCritical, "Quitting"

End Sub

Open in new window

Euro5Author Commented:
The code does not work as I need. It gives me an error when it should not.
I am going to give up on this one. Thanks for your effort here.
Roy CoxGroup Finance ManagerCommented:
My error code works fine. The problem is with your other code. Try this, I have amended to whole code
Sub FILTER()
    Dim rngData As Range
    Dim i As Integer
    Set rngData = Sheets("Report").Range("A1").CurrentRegion
    i = Application.WorksheetFunction.Match("Rated Weight", rngData.Rows(1), 0)

    rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value


    ' if an error occurs go to the end and give a message, then quit
   
    If rngData.SpecialCells(xlCellTypeVisible).Offset(1).Rows.Count > 1 Then
        rngData.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else: MsgBox "No rows to delete", vbCritical, "Quitting"
    If rngData.Parent.AutoFilterMode = True Then rngData.Parent.AutoFilterMode = False
    End If
    ' any other code
:
    If rngData.Parent.AutoFilterMode = True Then rngData.Parent.AutoFilterMode = False
    
End Sub

Open in new window

Euro5Author Commented:
Roy, I tried this and it ran straight through with no error code, even though there was no data.
I'm sure that I am doing something wrong if it runs for you, but I really can't figure it out.
Roy CoxGroup Finance ManagerCommented:
here's your example with the new code. When the Weighted Rate value on the Report Sheet is 186 there no rows need deleting and the message runs. Change it to 174 and the row is deleted
test-for-no-rows-for-analysis.xlsm
Euro5Author Commented:
Roy,
If the Weighted value on the report sheet is 186, it is >= the 175 on the detail and no message should run.
I only need the message to run if there are no rows, to let the user know there was no data within the criterion.

When I run the above, I just get a message box with message "$Y$1"$Y$2 every time, no matter what is in the box.

Please help.
Roy CoxGroup Finance ManagerCommented:
This message will only run if there are no rows below the header row o the report.

There is no msgbox in Filter code to display the message "$Y$1"$Y$2
Sub FILTER()
    Dim rngData As Range
    Dim i As Integer
    Set rngData = Sheets("Report").Range("A1").CurrentRegion
    i = Application.WorksheetFunction.Match("Rated Weight", rngData.Rows(1), 0)

    rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value

    If rngData.Rows.Count = 1 Then
        MsgBox "No rows to delete", vbCritical, "Quitting"
        Exit Sub
    End If

    If rngData.SpecialCells(xlCellTypeVisible).Offset(1).Rows.Count > 1 Then
        rngData.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If

    If rngData.Parent.AutoFilterMode = True Then rngData.Parent.AutoFilterMode = False
    '    End If
    ' any other code
:
    If rngData.Parent.AutoFilterMode = True Then rngData.Parent.AutoFilterMode = False

End Sub

Open in new window

Euro5Author Commented:
Again, the message box should appear only if there are no rows remaining. If there are no rows that meet the criterion.
Right now it tells me that there are no rows to delete - but that is not helpful.
There is a row there - the rated weight 186.

What if there are NO ROWS there after the filter code runs?
I don't want an error - so I need a msg box and stop code.

I think there may be some confusion about the point.
Roy CoxGroup Finance ManagerCommented:
I only need the message to run if there are no rows, to let the user know there was no data within the criterion.

I have provided code to run if there are no rows.

I have provided alternative code that runs the message if no rows meet the criteria for deleting which is what I would expect.

What if there are NO ROWS there after the filter code runs?
I don't want an error - so I need a msg box and stop code.

Will this include a header row?
Euro5Author Commented:
Yes, it will include a header row.

Your code works IF THERE ARE NO ROWS TO DELETE.
That is not the same.

I need the code to initiate msg box when there is NO DATA AFTER THE DELETE.
Does that help?
Roy CoxGroup Finance ManagerCommented:
So you mean if everything is deleted?
Euro5Author Commented:
Yes, if there is no data except for header
Roy CoxGroup Finance ManagerCommented:
I'll have a look later.
Zack BarresseCEOCommented:
For your specific statement, there are ways of checking your data beforehand if there is data meeting your criteria. I would probably just opt for a couple of read-only properties pointing to your cells, then use that in a simple Evaluate statement in a function. Run the function, get your results. This should boil down to a one-liner. Here is what I would use...

Function DataWeightCheck( _
         Optional ByVal CheckRange As Range, _
         Optional ByVal Weight As Double _
         ) As Boolean
'
' Checks if data values have data which would be filtered by set minimum values (weight & deficit).
'
' Syntax:       DataWeightCheck([CheckRange],[Weight])
'
' Parameters:   CheckRange. Range. Optional. Range to look in. If omitted then column H of the Data sheet is used, excluding header.
'               Weight. Double. Optional. The minimum value CheckRange must meet. If omitted the Minimum Package Weight is used.
'
    If Weight = 0 Then Weight = MinimumWeight
    If CheckRange Is Nothing Then Set CheckRange = Data.Range("H2", Data.Cells(Data.Rows.Count, "H").End(xlUp))
    DataWeightCheck = CBool(Evaluate("COUNTIF('" & CheckRange.Parent.Name & "'!" & CheckRange.Address & ","">=""&" & Weight & ")") > 0)
End Function



' Minimum Package Weight
Public Property Get MinimumWeight() As Double
    MinimumWeight = Details.Range("MinimumWeight").Value
'    MinimumWeight = Details.Range("A3").Value 'Use if you don't name the range
End Property



'Minimum Deficit
Public Property Get MinimumDeficit() As Double
    MinimumDeficit = Details.Range("Deficit").Value
'    MinimumDeficit = Details.Range("B3").Value 'Use if you don't name the range
End Property

Open in new window


In the two Property's you'll notice there are two lines. One has a string name, while the other has a cell address. I'd recommend naming those ranges. If you do name them, just ensure they match in the VBA. If you don't name them, remove the first line of each and uncomment the second line of each. It'll still work the same, but be less flexible and dynamic.

To use this function just call it in a normal routine. Examples below.

To check for the minimum weight:
    If Not DataWeightCheck(, MinimumWeight) Then
        MsgBox "There was no data meeting the minimum package weight", vbExclamation, "No Data"
        Exit Sub
    End If

Open in new window

To check for the deficit:
    If Not DataWeightCheck(, MinimumDeficit) Then
        MsgBox "There was no data meeting the minimum deficit", vbExclamation, "No Data"
        Exit Sub
    End If

Open in new window

To check for both at once (since, upon re-reading your post a half-dozen times, it looks like you're checking the same range for both numbers):
    If Not DataWeightCheck(, WorksheetFunction.Max(MinimumWeight, MinimumDeficit)) Then
        MsgBox "There was no data meeting the minimum specified values", vbExclamation, "No Data"
        Exit Sub
    End If

Open in new window


This gives you what you want: 1) to check if data in the range meets your criteria, and 2) to exit without doing anything.

You'll notice in the 'DataWeightCheck' function there is an optional Range parameter. I don't use it in the examples above, but you can specify it to any range you want. It should be the range of values you want to check though. If you want to specify another range of data other than the source (from the Data sheet), just pass that range as that parameter.

Also, I would recommend not keeping general routines in your ThisWorkbook class module.

HTH

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Does this do what you want

MessageBox appears after the code is run if all data is deleted

Sub FILTER()
    Dim rngData As Range
    Dim i As Integer
    Set rngData = Sheets("Report").Range("A1").CurrentRegion
    i = Application.WorksheetFunction.Match("Rated Weight", rngData.Rows(1), 0)

    rngData.AutoFilter Field:=i, Criteria1:="<" & Sheets("Details").Range("B3").Value
   
   
    If rngData.SpecialCells(xlCellTypeVisible).Offset(1).Rows.Count > 1 Then
        rngData.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
'    Else: MsgBox "No rows to delete", vbCritical, "Quitting"
    If rngData.Parent.AutoFilterMode = True Then rngData.Parent.AutoFilterMode = False
 If rngData.Rows.Count = 1 Then
    MsgBox "No rows to delete", vbCritical, "Quitting"
    Exit Sub
    End If
    ' any other code
:
    If rngData.Parent.AutoFilterMode = True Then rngData.Parent.AutoFilterMode = False
    
End Sub

Open in new window

Zack BarresseCEOCommented:
Am I missing something here? Wouldn't the formulaic approach I posted be better to check before you even run the damn thing? I feel like I'm missing something, because it seems a pretty obvious logical flow. ??

Zack
Euro5Author Commented:
Zack,
I agree, my goal is to avoid running if there is no data - and this seems perfect for what I need.
I removed code from ThisWorkbook and added the first min weight check to sub.
Can you help me with the error I am seeing? I wonder if I did something wrong....
attaching the whole code in case that helps.

    If Not DataWeightCheck(, MinimumWeight) Then
        MsgBox "There was no data meeting the minimum package weight", vbExclamation, "No Data"
        Exit Sub
    End If
MWT-TOOL-v8.5.xlsm
Euro5Author Commented:
errorerror2error
Euro5Author Commented:
Roy, No, unfortunately when I run it, no message is displayed even though there is no data.
Euro5Author Commented:
Thanks!
Roy CoxGroup Finance ManagerCommented:
When the filter macro removes all rows, i.e. after changing the value to 174 for each row in the report, the message is shown
test-for-no-rows-for-analysis--1-.xlsm
Zack BarresseCEOCommented:
No problem Euro5. If you read my post carefully I explain about those Property's. There are two lines of code in each. One is not commented out (the line you're getting an error on) and another one is commented out. The former is using a named range, which you would need to add those names in order for it to work. The latter will work as-is without you doing anything else to your file, but it would require you to uncomment this second line and comment/remove the first line.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.