1

I only have 1 sheet named Not-Covered-Time, my goal is to copy the details from the Not-Covered-Time into other sheet and only copy rows base on the cell value "0" that is located in Column T (T1).

Sub fastRowDelete()
'@IgnoreModule
     Dim orig As Worksheet
     Dim name_switch As String
     Dim output As Worksheet
     Dim count_col As Long
     Dim count_row As Long


     Set orig = ActiveSheet
     name_switch = orig.Name

     If orig.Name = "Not-Covered-Time" Then
        orig.Name = "Not-Covered-Time1"

        Else
        orig.Name = "Not-Covered-Time"
     End If

     Sheets.Add.Name = name_switch

     Set output = Sheets(name_switch)

    orig.Activate
    count_col = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
    count_row = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

' filter
ActiveSheet.Range("A1").AutoFilter Field:=20, Criteria1:="0"

' Copy data over
orig.Range(orig.Cells(1, 1), orig.Cells(count_row, count_col)). _
SpecialCells(xlCellTypeVisible).Copy
output.Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
orig.ShowAllData
orig.AutoFilterMode = False

'delete original sheet
Application.DisplayAlerts = False
orig.Delete
Application.DisplayAlerts = True

output.Activate
ActiveSheet.Range("A1").Select

End Sub

A reply is very much appreciated. Thank you!

Have tried everything and the code looks exactly right but there's something going on that causes the error application defined or object error 1004

Winter
  • 11
  • 2
  • Change Criteria to Criteria1 on the filter part. – Joel Nov 01 '22 at 10:45
  • `orig.Range(Cells(1, 1), Cells(count_row, count_col))` --> `orig.Range(orig.Cells(1, 1), orig.Cells(count_row, count_col))` – BigBen Nov 01 '22 at 15:35
  • Careful, you seem to have [implicit ActiveSheet references](https://rubberduckvba.com/Inspections/Details/ImplicitActiveSheetReference) here. – Mathieu Guindon Nov 01 '22 at 16:47
  • Appreciated the response. Have tried what you guys suggested but same results, Error 1004. Thank you. – Winter Nov 02 '22 at 00:13

1 Answers1

1

The Criteria is missing the "1" on the name of parameter ("Criteria1:=").

Change this line

ActiveSheet.Range("A1").AutoFilter Field:=20, Criteria:="0"

To this

ActiveSheet.Range("A1").AutoFilter Field:=20, Criteria1:="0"
Arthur Boff
  • 11
  • 3
  • Hi, I already did. But same error. orig.Activate count_col = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight))) count_row = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown))) ' filter ActiveSheet.Range("A1").AutoFilter Field:=20, Criteria1:="0" ' Copy data over orig.Range(Cells(1, 1), Cells(count_row, count_col)). _ SpecialCells(xlCellTypeVisible).Copy output.Cells(1, 1).PasteSpecial xlPasteValues Application.CutCopyMode = False orig.ShowAllData orig.AutoFilterMode = False Object Error 1004 – Winter Nov 02 '22 at 00:06