Macro run time error

April 11, 2011 at 22:24:34
Specs: Windows 7
Hi,

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
http://support.microsoft.com/kb/210684

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

my sample file is in
http://rapidshare.com/files/4570057...
it is a 96mb file

[code]

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


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)
Else
lastrow = 0
End If

Next j
MsgBox "finished checking the sheets"

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:=1, 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
MsgBox RowCount
End With

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

End With
wsStart.AutoFilterMode = False


Else

wsStart.AutoFilterMode = False


With wsStart
.Range(sWord).AutoFilter Field:=1, Criteria1:="=0" 'if my column contains a 0 in it filter that
MsgBox sWord
With .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible)
Final = .Count
MsgBox "final"
RowCount = Final - 1 ' to account for column name
MsgBox RowCount
End With

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


End With
wsStart.AutoFilterMode = False
MsgBox "I am editing the existing sheet"


End If
Next rng

End Sub

[/code]


See More: Macro run time error

Report •


#1
April 18, 2011 at 09:19:27
When i run your code it fails with AutoFilter; nothing to do with Copy worksheet:

It fails at this line:

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

And the error is:" Autofilter out of range error"

I'm not going to take the time to re-write your code, but i'd look for another option in regards to "Autofilter".


Report •
Related Solutions


Ask Question