Macro run time error

Lopez - Apr 12, 2011 at 12:18 AM
 LopezGG - Apr 12, 2011 at 08:50 PM


I have a peice of code that filters a column for zero values and returns the rowcount. I tried looping over different columns.this macro works well with small input.
But I have an excel sheet with 160106 rows. I want to run my macro on this . I am getting a run 1004 error.I found the following link which kind of explains the problem

But I am not able to resolve it. Could anyone please help me. I am pasting my macro below

my sample file is in
it is a 96mb file


Option Explicit
Sub findrcn()
Dim wsStart As Worksheet
Dim sWord As String
Dim RowCount As Integer
Dim i As Long
Dim j As Long
Dim l As Long
Dim k As String
Dim Final As Integer
Dim lastrow As Integer
Dim rng As Range
Dim oBook As Workbook

Set wsStart = ActiveSheet
'this loop is to check if a sheet exists
For j = 1 To Worksheets.Count
k = Worksheets(j).Name
If UCase(k) = UCase("Analysis") Then
lastrow = ((Sheets("Analysis").Range("A" & Rows.Count).End(xlUp).Row) + 1)
lastrow = 0
End If

Next j
MsgBox "finished checking the sheets"
i = 1
For Each rng In Range("A1:B1").Columns
sWord = Replace(rng.Address(RowAbsolute:=False), "$", "") ''Now I am trying to loop over all the columns

If lastrow = 0 Then
Sheets.Add After:=Sheets(Sheets.Count) 'Adding a new sheet
Sheets(Sheets.Count).Name = "Analysis"
wsStart.AutoFilterMode = False

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

With .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
Final = .Count 'get the count of the number of rows after the filter
RowCount = Final - 1
End With

Sheets("Analysis").Range("A") = RowCount 'paste it in the analysis tab
Sheets("Analysis").Range("B") = (Range(sWord))

End With
wsStart.AutoFilterMode = False
MsgBox "Trust in the Lord with all your heart and lean not on your own understanding; In all your ways acknowledge Him, and He will make your paths straight." & vbCrLf & "Proverbs 3:5" & vbCrLf & " SUCCESSFULLY COMPLETED!!!"


wsStart.AutoFilterMode = False

lastrow = ((Sheets("Analysis").Range("A" & Rows.Count).End(xlUp).Row) + 1)
With wsStart
.Range(sWord).AutoFilter Field:=i, Criteria1:="=0" 'if my column contains a 0 in it filter that
With .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
Final = .Count
RowCount = Final - 1 ' to account for column name
End With

Sheets("Analysis").Range("A" & lastrow) = RowCount 'paste it in the analysis tab
Sheets("Analysis").Range("B" & lastrow) = (Range(sWord))

End With
wsStart.AutoFilterMode = False

End If
i = i + 1
Next rng


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"
Actually, i think I caused this error as I removed the filter before running it, so you can disregard it.

What is the analysis tab supposed to look like once the code has been run?
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.


Option Explicit
Public Sub TallyZerosOnActiveSheet()

Dim SourceSh As Worksheet
Dim CurrentCountRange As Range
Dim ZeroCount As Integer
Dim NextRow As Long
Dim ColWithHdr

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


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
change the DIMs of the variables from INTEGER to LONG. this should help you not having to split the lines into 25000 batches/
so for the first header TRA1 the numbers would be:
TRA1 147057 13048

Is this right?

Thanks a million .. It works after I changed it to long .. I got whole thing figured out .. am so glad .. thank you very much ray