Prev: Random Numbers
Next: Summary sheet including only open projects.
From: Mikmo on 25 Mar 2010 05:32

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
From: Dave O on 25 Mar 2010 10:11
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
From: Dave Peterson on 25 Mar 2010 10:12
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
 | 
Pages: 1
Prev: Random Numbers
Next: Summary sheet including only open projects.