Macro help

[AussieVic]

Hi would like to add a condition to the below code, when the code filtersinplace but there is no data to filter then do no proceed in copying to destination.



ShtTemp.Range("A5").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
Criteriarange:=Range("rngAsset"), Unique:=False


ShtTemp.Range("A5").CurrentRegion.Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")

 

[AussieVic]

*bump*

 

[AussieVic]

*bump*

 

[GaryWMn]

How about wrapping your copy line in this If statement?

ShtTemp.Range("A5").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
Criteriarange:=Range("rngAsset"), Unique:=False
If ShtTemp.Range("A5").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas.count > 1 OR _
ShtTemp.Range("A5").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.count > 1 then _
ShtTemp.Range("A5").CurrentRegion.Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")

Let us know if this works.

Gary

 

[AussieVic]

Hi Gary is it possible to exit sub if there is no data when filetered, so the code does not carry on

How about wrapping your copy line in this If statement?

ShtTemp.Range("A5").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
Criteriarange:=Range("rngAsset"), Unique:=False
If ShtTemp.Range("A5").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas.count > 1 OR _
ShtTemp.Range("A5").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.count > 1 then _
ShtTemp.Range("A5").CurrentRegion.Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")

Let us know if this works.

Gary

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] SomeCode()

    [COLOR="Blue"]Dim[/COLOR] rng [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]With[/COLOR] ShtTemp.Range("A5").CurrentRegion
        .AdvancedFilter Action:=xlFilterInPlace, Criteriarange:=Range("rngAsset"), Unique:=[COLOR="Blue"]False[/COLOR]
        [COLOR="Blue"]Set[/COLOR] rng = .SpecialCells(xlCellTypeVisible)
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] rng [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
            .Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[AussieVic]

Sektor will this exit sub if there is no data so the code does not keep running?

Sub SomeCode()

    Dim rng As Range
    
    With ShtTemp.Range("A5").CurrentRegion
        .AdvancedFilter Action:=xlFilterInPlace, Criteriarange:=Range("rngAsset"), Unique:=False
        Set rng = .SpecialCells(xlCellTypeVisible)
        If Not rng Is Nothing Then
            .Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")
        End If
 
        'more code
 

    End With
    
End Sub

 

[Sektor]

With exit:

[COLOR="Blue"]Sub[/COLOR] SomeCode()

    [COLOR="Blue"]Dim[/COLOR] rng [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]With[/COLOR] ShtTemp.Range("A5").CurrentRegion
        .AdvancedFilter Action:=xlFilterInPlace, Criteriarange:=Range("rngAsset"), Unique:=[COLOR="Blue"]False[/COLOR]
        [COLOR="Blue"]Set[/COLOR] rng = .SpecialCells(xlCellTypeVisible)
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] rng [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
            .Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")
        [COLOR="Blue"]Else[/COLOR]
            [B][COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR][/B]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[AussieVic]

That did not worked, when the data was filtered there was nothing there except for header row so i expect the code to Exit, but when i stepped through, it went to .offset etc and then End if

With exit:

[COLOR=blue]Sub[/COLOR] SomeCode()
 
    [COLOR=blue]Dim[/COLOR] rng [COLOR=blue]As[/COLOR] Range
 
    [COLOR=blue]With[/COLOR] ShtTemp.Range("A5").CurrentRegion
        .AdvancedFilter Action:=xlFilterInPlace, Criteriarange:=Range("rngAsset"), Unique:=[COLOR=blue]False[/COLOR]
        [COLOR=blue]Set[/COLOR] rng = .SpecialCells(xlCellTypeVisible)
        [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] rng [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
            .Offset(1).Resize(, 14).Copy Destination:=shtAsset.Range("A6")
        [COLOR=blue]Else[/COLOR]
            [B][COLOR=blue]Exit[/COLOR] [COLOR=blue]Sub[/COLOR][/B]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Sektor]

Your original question: "Sektor will this exit sub if there is no data so the code does not keep running?"
I wrote code which makes Sub terminate when there's no data. When do you want to exit? Please, be more specific.