StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMacro run time error
text
Body
copied!<p>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 <a href="http://support.microsoft.com/kb/210684" rel="nofollow">http://support.microsoft.com/kb/210684</a></p> <p>But I am not able to resolve it. Could anyone please help me. I am pasting my macro below</p> <p>my sample file is in <a href="http://rapidshare.com/files/457005707/data1.xlsx" rel="nofollow">http://rapidshare.com/files/457005707/data1.xlsx</a> it is a 96mb file </p> <pre><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 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) Else 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!!!" Else 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload