Why am I getting this VBA error?
 

I'm getting error 13 "type mismatch" when I run the below code. I've tested it on a small new excel sheet and it work fine. There are no other macros in my excel sheet so no conflicts.

Any ideas?

I'm trying to hide all rows were the value in column A of that row is blank / 0.


Sub blank_rows()

Dim ws As Worksheet
Dim wb As Workbook
Dim hide_rw As Long

Application.EnableEvents = False

Set wb = ActiveWorkbook
Dim end_row As Integer
For Each ws In wb.Worksheets
end_row = ws.Range("A65536").End(xlUp).Row 'this assumes the last value is in Column A.
For r = 1 To end_row 'change the 4 to be the first row you wish to check
If ws.Cells(r, 3).Value = "" Then
ws.Rows(r).Hidden = True
End If
Next
Next

Application.EnableEvents = True
End Sub

Why am I getting this VBA error?
 

This is your problem:

Dim end_row As Integer

An Integer variable maxes out at a value of 32,767. If you declare
that as Long your code should work properly. It likely worked on your
test sprdsht because that sprdsht used less than 32,767 rows.

Dave O
Eschew Obfuscation

Why am I getting this VBA error?
 

If the cell contains an error, then this line will fail:
If ws.Cells(r, 3).Value = "" Then

Instead, you could just check what's displayed in the cell:
If ws.Cells(r, 3).Text = "" Then

Mikmo wrote:

I'm getting error 13 "type mismatch" when I run the below code. I've
tested it on a small new excel sheet and it work fine. There are no
other macros in my excel sheet so no conflicts.

Any ideas?

I'm trying to hide all rows were the value in column A of that row is
blank / 0.

Sub blank_rows()

Dim ws As Worksheet
Dim wb As Workbook
Dim hide_rw As Long

Application.EnableEvents = False

Set wb = ActiveWorkbook
Dim end_row As Integer
For Each ws In wb.Worksheets
end_row = ws.Range("A65536").End(xlUp).Row 'this assumes the
last value is in Column A.
For r = 1 To end_row 'change the 4 to be the first row you wish
to check
* If ws.Cells(r, 3).Value = "" Then*
ws.Rows(r).Hidden = True
End If
Next
Next

Application.EnableEvents = True
End Sub

--
Mikmo

--

Dave Peterson