VBA AutoFilter

[OX_2005]

Need a little help from the Excel Guru's out there.

I have a VBA AutoFilter set up and it works except for 1 small problem. If the auto filter returns with no data how can I with the code I have tell it after TBL.Range.AutoFilter Field:=12, Criteria1:="Term" to skip everything and start back after EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete?
LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
EE.Range("A2:L" & LR).Copy
TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
Application.DisplayAlerts = False
EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
TBL.AutoFilter.ShowAllData

 

[Alex Blakenburg]

Try replacing your block of code that Starts with If and Finishes in End with this,

    If TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Address Then
        Application.DisplayAlerts = False
        With TBL.DataBodyRange.SpecialCells(xlCellTypeVisible)
            .Copy
            TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
            .Rows.Delete
        End With
        Application.DisplayAlerts = True
    End If

 

[OX_2005]

So I added the code and it can back with an error "No Cells were Found". The thing is sometimes there will be cells with "Term" and sometimes there wont and when there is not I need it to skip over the copy, paste, & delete. With the code like it is below the message box will read correctly when there are cells left visible after the auto filter and when there are no cells visible after auto filter. The issue I am having is when there are no rows visible it wants to continue to copy, paste, & delete instead of skip and end if to show all data.

Sub Add_Update_Employees()

Dim VT As Worksheet
Dim AT As Worksheet
Dim EE As Worksheet
Dim AB As Worksheet
Dim TERM As Worksheet
Dim LR As Long
Dim TBL As ListObject

Set VT = ThisWorkbook.Sheets("Vaca Tracker")
Set AT = ThisWorkbook.Sheets("Attend Tracker")
Set EE = ThisWorkbook.Sheets("Employees")
Set AB = ThisWorkbook.Sheets("Accrual Balances")
Set TERM = ThisWorkbook.Sheets("Term")
Set TBL = EE.ListObjects("Employees")


'   Unprotecting Worksheets

    VT.Unprotect
    AT.Unprotect

'   Not allowing screen to update

Application.ScreenUpdating = False

'   Auto Filter Term & Delete

LR = ThisWorkbook.Worksheets("Employees").Cells(Rows.Count, 1).End(xlUp).Row
   TBL.Range.AutoFilter Field:=12, Criteria1:="Term"
   MsgBox TBL.AutoFilter.Range.Columns(12).SpecialCells(xlCellTypeVisible).Cells.Count - 1
   If TBL.Range.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
   EE.Range("A2:L" & LR).Copy
   TERM.Range("A1").End(xlDown).Offset(1).PasteSpecial (xlPasteValues)
   Application.DisplayAlerts = False
   EE.Range("A2:L" & LR).SpecialCells(xlCellTypeVisible).Delete
   End If
   TBL.AutoFilter.ShowAllData

 

[Alex Blakenburg]

Although the line below seems to be what is most frequently used, it doesn't work when the filter returns a single contiguous block of rows that start in the row under the heading. ie if the heading is in row 1 and the filter only returns row 2 and any additional rows that under that with no hidden rows in between.

  If TBL.Range.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then

replace that line with

    If TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Address Then

put a breakpoint at Application.DisplayAlerts = False
and if it gets to that and you believe it shouldn't show me a picture of the filtered table at that point in time.
(Be sure to include the row and column references in the picture.)

 

[OX_2005]

If there is a cell with Term listed then it selects correctly to copy and paste. (I cleared the auto filter after is Selected the data)

If there is no cell with Term Listed t Selects all the data and copies and paste. (I cleared the auto filter after is Selected the data)

Where if there is no Cell with Term it should be going to End If

 

[Alex Blakenburg]

That is really weird. I assume that when you say you cleared the autofilter it meant that when you went back the sheet in the no Term scenario there were no visible cells (showing that the filter has been applied an no records returned)
I see you have a message box what did that show when there was no Term ?
If the value in the message box = 0 then can you put these 2 lines just before or just after the message box and tell me what it says in the immediate window (ctlr+G if you can't see the window). Maybe do it even if the message box is 0.

    Debug.Print "Visible Cells: ", TBL.Range.SpecialCells(xlCellTypeVisible).Rows.Address
    Debug.Print "Header Cells: ", TBL.HeaderRowRange.Address

Are you able to share a sample workbook via Dropbox, Google Drive, Onedrive etc with just your test sheet in Post #14 and your code in it ?
Just make sure it still has the issue at your end in that workbook (so it will need to have blank Term sheet for you to try it) and also make sure the shared workbook is available to anyone with the link.

 

[Akuini]

@Alex Blakenburg
I think the addresses are different because there are hidden columns, i.e H:J.

 

[Alex Blakenburg]

Thanks @Akuini that would certainly do it. I guess I just assumed that was only to condense the image, appreciate your input.

@OX_2005, in case you do have columns hidden, change the If statement line to the one below.
I does rely however on the first column in the table not being hidden.

If TBL.ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Rows.Address <> TBL.HeaderRowRange.Cells(1).Address

 

[OX_2005]

Thank you for this info I did not realize hidden columns would affect the way the Macro works. With the last VBA code you gave me it is now working correctly. Sorry for all the back and forth I really appreciate all your help with this. I am still very new to VBA and trying to learn as much as I can as I continue to do projects.

 

[Alex Blakenburg]

Thanks for the feedback and glad we could help. @Akuini's input definitely helped reduce the number of back and forths

 

[OX_2005]

Yes it did. @Akuini Thank you for your help in this as well