The intended effect: The code filters the table (Table1) on Sheet("AutoCensusReport") according to the EditLine# autofilter conditions. Then, if there are any rows that ARENT hidden it selects the unhidden cells that aren't blank in range A and pastes them in the first empty cell in range A on Sheet("Edits") then it unfilters the Table and goes to the next editline# (REPEAT).
Current Issues: I keep getting errors - notibly for the showalldata sections, but other times if there aren't any filtered rows (or something is wonky with the filters???) it is copying and pasting even the hidden rows for some reason instead of skipping to the next edit.
The Code:
Current Issues: I keep getting errors - notibly for the showalldata sections, but other times if there aren't any filtered rows (or something is wonky with the filters???) it is copying and pasting even the hidden rows for some reason instead of skipping to the next edit.
The Code:
VBA Code:
Sub DemographicsThree()
'
' Demographics Macro 3
' Demographics Edits 21-27
'
'
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=44, Criteria1:="MO"
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=42, Criteria1:="="
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Dim Sht As Worksheet
Dim HidCnt As Long
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "21"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "State = MO, County = BLANK"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
Edit22:
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=42, Criteria1:="<>"
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=44, Criteria1:="<>MO"
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
MsgBox "No Errors Edit 21"
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "22"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "State not = MO, County not = BLANK"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
Edit23:
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=44, Criteria1:="="
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=54, Criteria1:="=", Operator:=xlOr, Criteria2:="US"
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
MsgBox "No Errors Edit 21"
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "23"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "State = BLANK, Country = BLANK or US"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
Edit24:
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=25, Criteria1:="MSEP"
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=44, Criteria1:=Array("KS", "MI", "MN", "ND", "WI", "IL", "IN")
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
MsgBox "No Errors Edit 21"
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "24"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Student Type = MSEP, State not = KS, MI, MN, NE, ND, WI, IL, IN"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
Edit25:
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=46, Criteria1:="="
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
MsgBox "No Errors Edit 21"
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "25"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Residency = BLANK"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
Edit26:
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=45, Criteria1:="="
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
MsgBox "No Errors Edit 21"
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "26"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Location = BLANK"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
Edit27:
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=34, Criteria1:=">4"
Worksheets("AutoCensusReport").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">0"
Set Sht = Sheets("AutoCensusReport")
If Sht.AutoFilterMode = True Then
HidCnt = Sht.Rows.Count - Sht.Range("A:A").SpecialCells(xlCellTypeVisible).Count
If HidCnt > 0 Then
MsgBox "No Errors Edit 21"
Tbl.AutoFilter.ShowAllData
GoTo Edit22
End If
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Edits Sheet").Select
Range("A2").Select
Selection.End(xlDown).Range("A2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "Demographics"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "27"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "L35 Cen Term Cum GPA > 4.0"
If Sht.AutoFilterMode = True Then
Tbl.AutoFilter.ShowAllData
End If
EndProc:
End Sub