Related:

- Macro run time error
- Poppy play time - Download - Horror
- Cmos checksum error - Guide
- Network error occurred - Guide
- Run macro when cell value changes - Guide
- We can’t review this decision because too much time has passed since your account was disabled. ✓ - Facebook Forum

## 2 replies

At which line in the code do you get the error?

When I run it it stops at this line:

.Range(sWord).AutoFilter Field:=i, Criteria1:="=0" 'if my column contains a 0 in it filter that

"Run-Time Error '1004': Autofilter method of Range class failed"

When I run it it stops at this line:

.Range(sWord).AutoFilter Field:=i, Criteria1:="=0" 'if my column contains a 0 in it filter that

"Run-Time Error '1004': Autofilter method of Range class failed"

thank you very much for yoru reply

the analysis tab is supposed to look like

TRA13 7

TRA6r 7

CAL1 207

TRA5r 15999

TCA1 20845

TCA2r 20845

GG1r 26227

EDP1 40747

EDP2 40747

PPP1 40747

GG5r 41033

GG6r 42072

PFL 56353

TCA3 57631

HOX2 58555

OPM4r 58555

OPM2 60807

GG13r 61749

PPP3r 61749

PPP4r 61749

PPP5r 61749

PPP7r 61749

HOX1 70081

TCA5r 77437

TCA6r 77437

BIO 79783

TRA15 79783

CAL2 80075

GG7r 80075

CAL4 82599

PPP6r 86208

GG2 86249

GG2r1 86249

GLB1 86887

GLB2 86887

PPC 87413

OPM1 87779

TCA7r 89653

GG10 90581

GG8r 93339

GG9r 93339

CAL3 98928

MEB 105003

PYC 106019

FC1r 106608

FDH 108229

GG12 108229

TRA14 108229

TRA11 112069

TCA4 120859

TCA4a 120859

ACK 123061

PTA 123061

TRA7 123061

GG4 127066

FC2 127115

MEA 128411

GG11 133319

TRA8 142607

PCK 145355

TRA1 147057

MGS1 147547

MGS2 147547

PHB4 148387

PHB8 148387

PHB9 148387

TRA2 148387

ADH 152431

LDH 154731

PLOI 154731

OPM3 160101

those are the exact numbers

a friend helped me with another peiceof code.I am pasting it below for your reference.

[code]

Option Explicit

Public Sub TallyZerosOnActiveSheet()

Dim SourceSh As Worksheet

Dim CurrentCountRange As Range

Dim ZeroCount As Integer

Dim NextRow As Long

Dim ColWithHdr

' CONFIG HERE !!

Const analysisSh As String = "Analysis"

Const SrcHdrRow As Integer = 1

'Set Source Sheet

Set SourceSh = ActiveSheet

'Make sure Anlaysis Sheet Exist

If Not SheetExists(analysisSh) Then

Sheets.Copy after:=ActiveSheet

ActiveSheet.Name = analysisSh

End If

With SourceSh

' Cycle thru all columns that have header

For Each ColWithHdr In .Rows(SrcHdrRow & ":" & SrcHdrRow).SpecialCells(xlCellTypeConstants, 2)

' Set range to count

Set CurrentCountRange = .Cells(1, ColWithHdr.Column).EntireColumn

' Count zeros

ZeroCount = Application.WorksheetFunction.CountIf(CurrentCountRange, 0)

' Log Zeros

With Sheets(analysisSh)

NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(NextRow, 1).Value = ColWithHdr.Value

.Cells(NextRow, 2).Value = ZeroCount

End With

Next ColWithHdr

End With

End Sub

Private Function SheetExists(sname) As Boolean

' Returns TRUE if sheet exists in the active workbook

Dim x As Object

On Error Resume Next

Set x = ActiveWorkbook.Sheets(sname)

If Err = 0 Then SheetExists = True _

Else SheetExists = False

End Function

/code

Initially the code did not work but after I split it into 25,000 lines it works. the current code finds teh number of zeros in each column. I want to add another part to it. I also want to get the number of non-zeros in the first column and zeros in the second column. In other words,

I have to filter zeros for column 2 and find find out the number of non zero rows in column1

similarly, I have to filter zeros for column 3 and find out the number of non zeros in column 1 and so on .. the column 1 stays same all the time.

Could youplease help me modify the code

the analysis tab is supposed to look like

TRA13 7

TRA6r 7

CAL1 207

TRA5r 15999

TCA1 20845

TCA2r 20845

GG1r 26227

EDP1 40747

EDP2 40747

PPP1 40747

GG5r 41033

GG6r 42072

PFL 56353

TCA3 57631

HOX2 58555

OPM4r 58555

OPM2 60807

GG13r 61749

PPP3r 61749

PPP4r 61749

PPP5r 61749

PPP7r 61749

HOX1 70081

TCA5r 77437

TCA6r 77437

BIO 79783

TRA15 79783

CAL2 80075

GG7r 80075

CAL4 82599

PPP6r 86208

GG2 86249

GG2r1 86249

GLB1 86887

GLB2 86887

PPC 87413

OPM1 87779

TCA7r 89653

GG10 90581

GG8r 93339

GG9r 93339

CAL3 98928

MEB 105003

PYC 106019

FC1r 106608

FDH 108229

GG12 108229

TRA14 108229

TRA11 112069

TCA4 120859

TCA4a 120859

ACK 123061

PTA 123061

TRA7 123061

GG4 127066

FC2 127115

MEA 128411

GG11 133319

TRA8 142607

PCK 145355

TRA1 147057

MGS1 147547

MGS2 147547

PHB4 148387

PHB8 148387

PHB9 148387

TRA2 148387

ADH 152431

LDH 154731

PLOI 154731

OPM3 160101

those are the exact numbers

a friend helped me with another peiceof code.I am pasting it below for your reference.

[code]

Option Explicit

Public Sub TallyZerosOnActiveSheet()

Dim SourceSh As Worksheet

Dim CurrentCountRange As Range

Dim ZeroCount As Integer

Dim NextRow As Long

Dim ColWithHdr

' CONFIG HERE !!

Const analysisSh As String = "Analysis"

Const SrcHdrRow As Integer = 1

'Set Source Sheet

Set SourceSh = ActiveSheet

'Make sure Anlaysis Sheet Exist

If Not SheetExists(analysisSh) Then

Sheets.Copy after:=ActiveSheet

ActiveSheet.Name = analysisSh

End If

With SourceSh

' Cycle thru all columns that have header

For Each ColWithHdr In .Rows(SrcHdrRow & ":" & SrcHdrRow).SpecialCells(xlCellTypeConstants, 2)

' Set range to count

Set CurrentCountRange = .Cells(1, ColWithHdr.Column).EntireColumn

' Count zeros

ZeroCount = Application.WorksheetFunction.CountIf(CurrentCountRange, 0)

' Log Zeros

With Sheets(analysisSh)

NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(NextRow, 1).Value = ColWithHdr.Value

.Cells(NextRow, 2).Value = ZeroCount

End With

Next ColWithHdr

End With

End Sub

Private Function SheetExists(sname) As Boolean

' Returns TRUE if sheet exists in the active workbook

Dim x As Object

On Error Resume Next

Set x = ActiveWorkbook.Sheets(sname)

If Err = 0 Then SheetExists = True _

Else SheetExists = False

End Function

/code

Initially the code did not work but after I split it into 25,000 lines it works. the current code finds teh number of zeros in each column. I want to add another part to it. I also want to get the number of non-zeros in the first column and zeros in the second column. In other words,

I have to filter zeros for column 2 and find find out the number of non zero rows in column1

similarly, I have to filter zeros for column 3 and find out the number of non zeros in column 1 and so on .. the column 1 stays same all the time.

Could youplease help me modify the code

Apr 12, 2011 at 01:55 PM

What is the analysis tab supposed to look like once the code has been run?