• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check if autofilter find blank cells

Hi ,

When I click on the link that has been posted , the only option available is to DELETE the file , not download it.

Can you post the correct link ?

Narayan
 
Hi ,

I ran the macro after selecting the month of March , and there was no error.

The fact is there is no data for the month of March. Data is available only for the months of January and February.

Narayan
 
Sorry, this is cod that I use.
Code: 
Sub Analise007()
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim ws007 As Worksheet
Dim wsDados As Worksheet
Dim r As Range
    Application.ScreenUpdating = False
        'Determina as guias como variáveis
        Set ws007 = ThisWorkbook.Worksheets("Analise Dep.007")
        Set wsDados = ThisWorkbook.Worksheets("Dados Consolidado")
        'Verifica a ultima célula e limpa dados
        lr = ws007.Range("E10").End(xlDown).Row - 10
        With ws007
            .AutoFilterMode = False
            .Range("E11:K" & .Rows.Count).ClearContents
        End With
        CD = ws007.Range("E8").Value
        MES = ws007.Range("G8").Value
        'Filtra e copia dados
        With wsDados
            .AutoFilterMode = False
            lr2 = .Cells(.Rows.Count, "A").End(xlUp).Row
           
            With .Range("A1:P1")
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:=CD
                .AutoFilter Field:=6, Criteria1:=MES
                'Set Rng = .Range("A2", Range("A" & Rows.Count).End(xlUp).SpecialCells(xlCellTypeVisible))
                'If Rng Is Nothing Then
                    .Range("O2:P" & lr2).SpecialCells(xlVisible).Copy ws007.Range("E11")
                '    Exit Sub
                'End If
            End With
        End With
        'Remove duplicidades
        With ws007
            .Range("E11:F" & .Range("E" & .Rows.Count).End(xlUp).Row).RemoveDuplicates Array(1, 2), xlYes
            .AutoFilterMode = False
            lr = ws007.Range("E10").End(xlDown).Row - 10
            [G11].Formula = "=IF(E11="""","""",SUMIFS('Dados Consolidado'!$R:$R,'Dados Consolidado'!$A:$A,$E$8,'Dados Consolidado'!$O:$O,$E11,'Dados Consolidado'!$U:$U,""<>SOBRA"",'Dados Consolidado'!$V:$V,$F$8,'Dados Consolidado'!F:F,$G$8))"
            [H11].Formula = "=IF(E11="""","""",SUMIFS('Dados Consolidado'!$T:$T,'Dados Consolidado'!$A:$A,$E$8,'Dados Consolidado'!$O:$O,$E11,'Dados Consolidado'!$U:$U,""<>SOBRA"",'Dados Consolidado'!$V:$V,$F$8,'Dados Consolidado'!F:F,$G$8))"
            [I11].Formula = "=SUMIFS('Dep 007'!$F:$F,'Dep 007'!$A:$A,E11,'Dep 007'!$C:$C,$E$8)"
            [J11].Formula = "=SUMIFS('Dep 007'!$G:$G,'Dep 007'!$A:$A,E11,'Dep 007'!$C:$C,$E$8) "
            [K11].Formula = "=IF(G11="""","""",IF(G11<>I1,""DIVERGENTE"",""CORRETO""))"
            Range("G11:K11").AutoFill Destination:=Range("G11:K" & lr)
            With Range("G11:K" & lr)
                .Value = .Value
            End With
            .Range("E10:K11").AutoFilter Field:=3, Criteria1:="<>0"
            .Range("E11", .Range("E" & .Rows.Count).End(xlUp)).Sort [E11], xlAscending
        End With
    Application.ScreenUpdating = True
End Sub
 
Hi ,

See if this works.
Code: 
Sub Analise007()
    Dim ws007 As Worksheet, wsDados As Worksheet
    Dim r As Range
    Dim lr As Long, lr2 As Long, lr3 As Long
   
    Application.ScreenUpdating = False
       
'  Determina as guias como variáveis
    Set ws007 = ThisWorkbook.Worksheets("Analise Dep.007")
    Set wsDados = ThisWorkbook.Worksheets("Dados Consolidado")

'  Verifica a ultima célula e limpa dados
    lr = ws007.Range("E10").End(xlDown).Row - 10
    With ws007
        .AutoFilterMode = False
        .Range("E11:K" & .Rows.Count).ClearContents
    End With
       
    CD = ws007.Range("E8").Value
    MES = ws007.Range("G8").Value
       
'  Filtra e copia dados
    With wsDados
        .AutoFilterMode = False
        lr2 = .Cells(.Rows.Count, "A").End(xlUp).Row
       
        On Error Resume Next
           
        With .Range("A1:P1")
              .AutoFilter
              .AutoFilter Field:=1, Criteria1:=CD
              .AutoFilter Field:=6, Criteria1:=MES
              Set rng = .Range("A2", Range("A" & Rows.Count).End(xlUp).SpecialCells(xlCellTypeVisible))
              If rng Is Nothing Then
                .Range("O2:P" & lr2).SpecialCells(xlVisible).Copy ws007.Range("E11")
                Exit Sub
              End If
        End With
         
        On Error GoTo 0
    End With
       
'  Remove duplicidades
    With ws007
        .Range("E11:F" & .Range("E" & .Rows.Count).End(xlUp).Row).RemoveDuplicates Array(1, 2), xlYes
        .AutoFilterMode = False
        lr = ws007.Range("E10").End(xlDown).Row - 10
        [G11].Formula = "=IF(E11="""","""",SUMIFS('Dados Consolidado'!$R:$R,'Dados Consolidado'!$A:$A,$E$8,'Dados Consolidado'!$O:$O,$E11,'Dados Consolidado'!$U:$U,""<>SOBRA"",'Dados Consolidado'!$V:$V,$F$8,'Dados Consolidado'!F:F,$G$8))"
        [H11].Formula = "=IF(E11="""","""",SUMIFS('Dados Consolidado'!$T:$T,'Dados Consolidado'!$A:$A,$E$8,'Dados Consolidado'!$O:$O,$E11,'Dados Consolidado'!$U:$U,""<>SOBRA"",'Dados Consolidado'!$V:$V,$F$8,'Dados Consolidado'!F:F,$G$8))"
        [I11].Formula = "=SUMIFS('Dep 007'!$F:$F,'Dep 007'!$A:$A,E11,'Dep 007'!$C:$C,$E$8)"
        [J11].Formula = "=SUMIFS('Dep 007'!$G:$G,'Dep 007'!$A:$A,E11,'Dep 007'!$C:$C,$E$8) "
        [K11].Formula = "=IF(G11="""","""",IF(G11<>I1,""DIVERGENTE"",""CORRETO""))"
        Range("G11:K11").AutoFill Destination:=Range("G11:K" & lr)
        Range("G11:K" & lr).Value = Range("G11:K" & lr).Value
           
        .Range("E10:K11").AutoFilter Field:=3, Criteria1:="<>0"
        .Range("E11", .Range("E" & .Rows.Count).End(xlUp)).Sort [E11], xlAscending
    End With
   
    Application.ScreenUpdating = True
End Sub
Narayan
 
You must log in or register to reply here.
Back
Top