Deleted Rows reappearing in table

LauraJane

New Member
Joined
Feb 9, 2015
Messages
7
I've created a macro that takes a master spreadsheet, and creates a new spreadsheet for each school listed in the table. I just got a new laptop and installed Excel 365 on it. I copied the VBA code to the new machine, but when I ran it, each new worksheet still contained the data for all the schools, not just the school for that particular file. I stepped through the code, and the schools did delete, but when it got to the part where the filter was removed from the table (ws.ListObjects("Data").AutoFilter.ShowAllData), all the schools reappeared. I'm stumped on why this is happening - It didn't happen on the other two machines and other iterations of the file that I've used this macro on. I don't know if it's an Excel setting or a setting on this particular master file. The other two machines - one used Excel 365, and the other Excel 2016.

VBA Code:
Sub CreateGalileoSchoolFiles()
Dim i As Integer, wb As Workbook, schools() As Variant, schools_to_delete() As Variant
Dim ws As Worksheet, rng As Range, dt As String
schools = SchoolsInList()
dt = MonthName(Month(Now)) & " " & Year(Now)

Set wb = ActiveWorkbook
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

For i = 1 To UBound(schools)
    wb.SaveCopyAs ("Galileo " & dt & " " & schools(i) & ".xlsx")
    Workbooks.Open ("Galileo " & dt & " " & schools(i) & ".xlsx")
    Workbooks("Galileo " & dt & " " & schools(i) & ".xlsx").Activate
    Set ws = Sheets("Data")
    ws.Activate
    schools_to_delete = schools
    schools_to_delete(i) = "x"
    Set rng = ws.ListObjects("Data").DataBodyRange
    
    With ws
        .AutoFilterMode = False
        ws.ListObjects("Data").Range.AutoFilter Field:=18, Criteria1:= _
        Array(schools_to_delete), Operator:=xlFilterValues
        ws.Range(rng.Address).SpecialCells(xlCellTypeVisible).Delete
        .AutoFilterMode = False
        ws.ListObjects("Data").AutoFilter.ShowAllData
    End With
    
    ActiveWorkbook.RefreshAll
    Call SelectA1
    ActiveWorkbook.Save
    ActiveWorkbook.Close
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub
Function SchoolsInList() As Variant
Dim schools() As String
Dim C As Collection
Dim r As Range
Dim i As Long
Dim last_row As Long
last_row = Cells(Rows.Count, 1).End(xlUp).Row
Set C = New Collection
On Error Resume Next
For Each r In Worksheets("Data").Range("R2:R" & last_row).Cells
    C.Add r.Value, CStr(r.Value)
Next

On Error GoTo 0

ReDim A(1 To C.Count)

For i = 1 To C.Count
    A(i) = C.Item(i)
Next i

SchoolsInList = A

End Function


Sub SelectA1()
Dim i As Long
For i = 1 To ActiveWorkbook.Sheets.Count
Sheets(i).Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Next i
ActiveWorkbook.Worksheets(2).Activate
End Sub
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try changing this
Code:
ws.Range(rng.Address).SpecialCells(xlCellTypeVisible).Delete
To this
Code:
rng.SpecialCells(xlCellTypeVisible).Delete

Also, you do not need this line
Code:
ws.ListObjects("Data").AutoFilter.ShowAllData
since it baically duplicates the AutoFilterMode = False statement.
 
Last edited:

LauraJane

New Member
Joined
Feb 9, 2015
Messages
7
The data reappeared again - Although with a closer look when I cleared the filter on the field while the code was stopped on the .AutoFilterMode = False line, I saw that the row numbers were not contiguous - as if there were hidden rows. When I originally checked, I selected "Show All" on the field list, and only one school showed. This time I cleared filters and all schools showed back up. So the rows are being hidden, and not being deleted.
 

LauraJane

New Member
Joined
Feb 9, 2015
Messages
7
Try changing this
Code:
ws.Range(rng.Address).SpecialCells(xlCellTypeVisible).Delete
To this
Code:
rng.SpecialCells(xlCellTypeVisible).Delete

Also, you do not need this line
Code:
ws.ListObjects("Data").AutoFilter.ShowAllData
since it baically duplicates the AutoFilterMode = False statement.

When I comment out the
Code:
ws.ListObjects("Data").AutoFilter.ShowAllData
, and run the macro, the table filter doesn't clear.
 

LauraJane

New Member
Joined
Feb 9, 2015
Messages
7

ADVERTISEMENT

I found the problem - the AutoFilter = False statement at the beginning of the with statement didn't actually clear the table filters. There was a filter on the table, and then the second filter was placed on the table and then that visible data was deleted. When the ws.ListObjects("Data").AutoFilter.ShowAllData line executed, it cleared both filters, leaving me with rows of schools that should have been deleted had the first filter been cleared. I added the .ShowAllData line of code to the beginning of the With statement to avoid the same problem in the future.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I don't like working with tables in VBA because the coding is so cumbersome to deal with the table attributes. Thanks for the feedback.
Regards, JLG
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,012
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I don't like working with tables in VBA because the coding is so cumbersome to deal with the table attributes
Fully agree with this comment, but I've been howled down by others for making it ..... :devilish:
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
It seems kind of redundant to use tables or pivot tables and VBA. I suppose there are some advantages to having a table, but the VBA can do everything that the table does, so why not just use a standard spreadsheet and manipulate the data with VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,622,002
Members
415,873
Latest member
fuulhouse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top