Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

doesn't filter data when save new file

0

Hello 

I try  when  save  the  file  as xlsx  should  be  filter  by  delete  any  row  contains zero for  column D,E  together I  put  the  expected  result  in  second  file  how  should  be  after export  the  file.  

Sub EXPORT_REPORT()
Dim wsData As Worksheet, StrPath As String
Dim OUTrng As Range

    Application.DisplayAlerts = False
    'For Each ws In ThisWorkbook.Worksheets
    StrPath = ActiveWorkbook.Path & "\"
       Set wsData = ActiveSheet.Cells(1).CurrentRegion.Clear
        With ThisWorkbook
   Set OUTrng = .ActiveSheet.Range("A1").CurrentRegion
    With OUTrng
        .AutoFilter 4, ""
        .AutoFilter 5, ""
        Application.DisplayAlerts = False
        .Offset(1).SpecialCells(xlCellTypeVisible).Delete
        Application.DisplayAlerts = True
        .AutoFilter
    End With
    End With
 x = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("a2:a" & x) = Evaluate("row(1:" & x & ")")
        ActiveSheet.Copy

        ActiveWorkbook.SaveAs Filename:= _
            StrPath & " REPORT  " & Format(Application.WorksheetFunction.EoMonth(Date - 10, 0), "MM - YYYY") & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Answer
Discuss

Discussion

Mussa

Sorry but your question is hard to understand. Firstly, in FILTER.xlsm, your early line:
Set wsData = ActiveSheet.Cells(1).CurrentRegion.Clear
 
deletes all the data you are trying to filter.

Secondly, do you really want to remove rows which have empty cells in column 4 OR 5 (rather than if both are blank)? Your REPORT file seems to show that you want to report items where there is data (but is it in columns C, D and E?)

Please try to clarify in your question
John_Ru (rep: 3992) Sep 12, '22 at 1:20 pm
Hi John,
the  standard  is  for   columns D,E  together, if  they are both empty  or  zero  then  should  delete   the  whole  rows  contains empty or zero values  for cells  is  in columns D,E when  create  new  file  as  the  result  in  file  2 .  if  there  is  one  of  the  columns D or E contains  values then  shouldn't  delete the  rows  .
I  thought  the  file2   tell the  whole  story !!
Mussa (rep: 32) Sep 12, '22 at 2:52 pm
I don't see how file 2 shows the story since file 1 (FILTER) does not have empty cells in columns 4 and 5. I can't waste any more time on this today. Might look again tomorrow but I'm busy then and I really need your questions to be right first time.
John_Ru (rep: 3992) Sep 12, '22 at 3:15 pm
file 1 (FILTER) does not have empty cells in columns 4 and 5. 
you're wrong  sorry  I  said  that  !! but  this  the  truth .
you  can  see row2,8  contains empty cells for  column D,E  in  file  FILTER and  you  will find  theses rows  have  deleted in second  file  
OK  take  your  time if  you  still interest  .
Mussa (rep: 32) Sep 12, '22 at 3:38 pm
Add to Discussion

Answers

0
Selected Answer

Mussa

In the attached, I've modified your code (with changes and comments in bold below).

Your .Autofilter combination won't work as you intend so I have extended the OUTrng to add a helper column containing and OR formula (for D or E isn't blank). Ten it's autofilterd on that helper column and visible cells copired to a new sheet.

Sub EXPORT_REPORT()
Dim wsData As Worksheet, StrPath As String
Dim OUTrng As Range

Application.DisplayAlerts = False
'For Each ws In ThisWorkbook.Worksheets
StrPath = ActiveWorkbook.Path & "\"

With ThisWorkbook
    Set OUTrng = .ActiveSheet.Range("A1").CurrentRegion
    ' ### add helper column then filter on that
    With OUTrng
        Set OUTrng = OUTrng.Resize(.Rows.Count, .Columns.Count + 1)
    End With
    ' filter for D and E = blank or 0
    With OUTrng
        ' add formula to helper column then filter on that
        .Columns(.Columns.Count).Formula = "=If(OR(D" & .Row() & ">0, E" & .Row() & ">0),TRUE, FALSE)"
        .AutoFilter .Columns.Count, "TRUE"
        ' add new sheet and copy filtered data to that
        Worksheets.Add.Name = "Report"
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("Report").Cells(1, 1)
        .AutoFilter
        'delete helper columns
        .Columns(.Columns.Count).Delete
        ActiveSheet.Columns(.Columns.Count + 1).Delete
    End With
End With

x = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("a2:a" & x) = Evaluate("row(1:" & x - 1 & ")")

    ' remove source sheet
    Worksheets("sheet1").Delete

    ActiveWorkbook.SaveAs Filename:= _
        StrPath & "REPORT " & Format(Application.WorksheetFunction.EoMonth(Date - 10, 0), "MM - YYYY") & ".xlsx", FileFormat:=51

Application.DisplayAlerts = True

ActiveWorkbook.Close SaveChanges:=False

End Sub

Hope this makes sense and fixes things for you.

Discuss

Discussion

Hi John,
thanks  very  much  for  your answering .
Mussa (rep: 32) Sep 13, '22 at 8:44 am
Thanks for selecting my answer, Mussa. Did you get an email saying that the answer had been posted?
John_Ru (rep: 3992) Sep 13, '22 at 8:47 am
Did you get an email saying that the answer had been posted?
yes I  did  it .
Mussa (rep: 32) Sep 14, '22 at 4:17 am
Thanks Mussa. I'm not getting those enails for some reason  -(Don knows that but yet to fix) 
John_Ru (rep: 3992) Sep 14, '22 at 4:50 am
it  happens   stange  matters  recently  . it  seems  some  maintenance  for  the web  . for  example  when post  subject  it  takes at  least   almost  5 hours  to  see my  post  and  then  will post  it . I    no  know  if MR. Don   knows   that. . or  he  wants that !
Mussa (rep: 32) Sep 14, '22 at 3:41 pm
Mussa. I think the delay is because Don now manually moderates all questions (to avoid the spam attackes from before) and he's in a different time zone to both of us. This week I asked him about his plan to skip that for known users with a high reputation (like you). I'm waiting for his reply.
John_Ru (rep: 3992) Sep 14, '22 at 4:56 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login