don't copy filtered data if no active cells

[xander1981]

Oh Dear Just when I thought it was sorted, I come across a month that doesn't have any data to copy and so have found out the lngcount to see if data does exist is not working. Any idea?

' **************** Hong Kong to Hamburg ***********************************
    'reset lngCount
    lngCount = 0
    With Application.WorksheetFunction
        'count whether any shipment exist in May from Hong Kong to Hamburg
        lngCount = .CountIf(wksRawData.Columns(11), ">=" & May) _
        - .CountIf(wksRawData.Columns(11), ">" & Jun)
        lngCount = lngCount * .CountIf(wksRawData.Columns(14), "=" & HK)
        lngCount = lngCount * .CountIf(wksRawData.Columns(15), "=" & HAM)
    End With

[xander1981]

panic over. I replaced '*' with '&' now picks up if blank. Thanks again.

[Admin]

Hi

Ideally the * should be there. The logic is, it multiplies each countif results. So if any countif returns 0, then the code won't work.

[xander1981]

Very good point. I ran through the code line by line and found the lngCount was a - figure so changed code to the following. Do you think it would be ok? if lngCount > 0 copy data. That way only when there is at least 1 line of data will code copy the data else it will show message. TBH my brain is getting a little fried.

With Application.WorksheetFunction
        'count whether any shipment exist in May from Hong Kong to Kotka
        lngCount = .CountIf(wksRawData.Columns(11), ">=" & May) _
        - .CountIf(wksRawData.Columns(11), ">" & Jun) _
        * .CountIf(wksRawData.Columns(14), "=" & HK) _
        * .CountIf(wksRawData.Columns(15), "=" & KT)
    End With
    
    'if exist
    If lngCount > 0 Then

Thank you for being patient.

[Admin]

Hi

Not sure how a -figure comes.

split each count on different line of code rather than puting a single line.

With Application.WorksheetFunction
    'count whether any shipment exist in May from Hong Kong to Kotka
    lngCount = .CountIf(wksRawData.Columns(11), ">=" & May) - .CountIf(wksRawData.Columns(11), ">" & Jun)
    lngCount = lngCount * .CountIf(wksRawData.Columns(14), "=" & HK)
    lngCount = lngCount * .CountIf(wksRawData.Columns(15), "=" & KT)
End With

[xander1981]

Thanks Admin. I have spent a few hours on it but can't get it to work. the above code won't work The problem is I have to run the same code for each month of the year to copy data for shipment in that month for shipments from port1 to port2. The code we have filters the code to the month which is great but the problem with the code is :

With Application.WorksheetFunction
        'count whether any shipment exist in May from Hong Kong to Kotka
        lngCount = .CountIf(wksRawData.Columns(11), ">=" & May) _
        - .CountIf(wksRawData.Columns(11), ">" & Jun) _
        * .CountIf(wksRawData.Columns(14), "=" & HK) _
        * .CountIf(wksRawData.Columns(15), "=" & KT)
    End With

the main problem is that when using 'CountIf(wksRawData.Columns' this uses the whole sheet as range not just the month in question. So I need to filter month, then only look for shipments from port1 to port 2 within the month range.

[xander1981]

Perhaps we can check for shipments in the month (which we have) then set that as a range. We can then use that range to filter the ports and copy the data. Not sure how to code that though

[Admin]

If you could attach a sample workbook, I'll have a look.

[xander1981]

Thanks admin. Attaching now. The attached uses the month on June as the example. To summerise, it checks the whole sheet to see if any shipments but I need it to only check the date range (June). Thanks again Attachment 424

[xander1981]

I'm very nearly there. I just need the following code corrected so it only checks column 14 of visible rows.

lngCount = 0
    With Application.WorksheetFunction
        'count whether any shipment exist in June from Hong Kong to Kotka
        lngCount = rngRawData.SpecialCells(xlCellTypeVisible).CountIf(Columns(14), HK)
        End With

Thanks