Compile Error: Procedure too large

mlkenney0809

New Member
Joined
Jun 13, 2008
Messages
10
I am new to visual basic editor and I have a new position which right now has me copying and pasting a macro to hide all rows that contain zeros. I have attached a piece of it. There are 1,024 rows total and I got to row 479 and I get the error message.

Can you help me? It is telling me to "break up" the procedure, but I have no idea what that means or how to do it. There has got to be an easier way to do this.

Sub HideZeroCells()
If Range("g18") = "0" Then
Rows("18:18").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g22") = "0" Then
Rows("22:22").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g23") = "0" Then
Rows("23:23").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g24") = "0" Then
Rows("24:24").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g28") = "0" Then
Rows("28:28").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g29") = "0" Then
Rows("29:29").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g30") = "0" Then
Rows("30:30").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g31") = "0" Then
Rows("31:31").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g32") = "0" Then
Rows("32:32").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g34") = "0" Then
Rows("34:34").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g36") = "0" Then
Rows("36:36").Select
Selection.EntireRow.Hidden = True
End If
''
If Range("g37") = "0" Then
Rows("37:37").Select
Selection.EntireRow.Hidden = True
End If
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi

What's in the G column cells that are not neing tested/hidden? eg G33. If there are never zeros in these cells, then there should be a much faster way to achieve your aim.
 
Upvote 0
You can try a construct like the following:

Code:
Sub test()
Dim strRange As String, cell As Range
strRange = "G2,G3,G4,G6:G9,G10,G12,G15,G17,G18:G20,g23,g34,g56,g57,g58,g59,g51,g53,g54,g55,g59,g60"
For Each cell In Range(strRange)
    If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next
End Sub

The one pain is you have to create a string holding all the cells you want tested. If you have runs of cells eg G20 up to G30 then you can write that as G20:G30 in the string (as I have done above with G18:G20).
 
Upvote 0
Okay, thank you for that, I was able to do the string, but now the error I get is Compile Error: Sub or function not defined.
 
Upvote 0
Okay I need some more help now

I used your example above and was able to get only so far (see below) I need to go to row 1,034 but when I get to row 879 I get the error message below:

Run Time Error '1004'
Method'Range'of object'_'Global' failed
 
Upvote 0
Sorry here is the code I have:

Sub HideZeroCells()
Dim strRange As String, cell As Range
strRange = "G18,G20,G22:G24,G26,G28:G32,G34,G36:G97,G99,G101,G103,G105:G112,G114,G116:G123,G125,G127:G131,G133,G135:G141,G143,G145:G193,G195,G197:G198,G200,G202:G204,G206,G208,G210:G536,G538,G540:G815,G817,G819:G822,G824,G826:G842,G844,G846:G853,G855,G860,G862:G879,G881"
For Each cell In Range(strRange)
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next
End Sub
 
Upvote 0
Sub HideZeroCells()
For i=18 to 881
Select Case i
case 18,20,22 to 24,26,28 to 32,34,36 to 97,99,101,103,105 to 112,114,116 to 123,125,127 to 131,133,135 to 141,143,145 to 193, 195, 197 to 198, 200,202 to 204,206,208,210 to 536, 538,540 to 815,817, 819 to 822,824,826 to 842,844,846 to 853,855,860,862 to 879, 881
If cells(i,7).value=0 then rows(i).hidden=true
End Select
Next
End Sub
 
Upvote 0
There's a limit to how many characters you use in the Range...I think 255
You'd have to split it up into 2 or more loops.....

Like for example..

Code:
Sub HideZeroCells()
Dim strRange As String, cell As Range
strRange = "G18,G20,G22:G24,G26,G28:G32,G34,G36:G97,G99,G101,G103,G105:G112,G114,G116:G123,G125,G127:G131,G133,G135:G141,G143,G145:G 193,G195,G197:G198,G200,G202:G204"

For Each cell In Range(strRange)
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next
 
strRange = "G206,G208,G210:G536,G538,G540:G815,G817,G819:G822,G824,G826:G842,G844,G846:G853,G855,G 860,G862:G879,G881"
 
For Each cell In Range(strRange)
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next

End Sub
 
Upvote 0
Thank you soooooo much. I have never used macro before and I have never written code, but my NEW boss and I were trying to find a way to hide the rows with zeros in the G column so the spreadsheet would not be so large when printed.

My boss had started the code but he was writing code for EVERY row, it was taking forever to copy and paste and change the row number.

This forum just helped me complete the spreadsheet in half the time, not to mention I look fantastic to my new boss right now!:)

thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top