Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 427
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 - error saving file & error loading dll TinaF Excel Discussion (Misc queries) 0 July 1st 09 01:49 PM
Error of slope taking into account error of the data points cer144 Excel Worksheet Functions 5 July 7th 08 07:26 PM
Counting instances of found text (Excel error? Or user error?) S Davis Excel Worksheet Functions 5 September 12th 06 04:52 PM
I have Error 1919 Error Configuring ODBC dataSource Database Texanna1 Excel Discussion (Misc queries) 1 September 12th 06 06:35 AM


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"