Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - error saving file & error loading dll | Excel Discussion (Misc queries) | |||
Error of slope taking into account error of the data points | Excel Worksheet Functions | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) |