Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 16th, 2010, 03:09 PM
Registered User
 
Join Date: Jul 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Hide Empty Rows

If someone could help me out, I would greatly appreciate it. I am trying to hide rows with a 0 value in a list. I would prefer to do it dynamicaly without VBA but I don't think it's possible. So what I have in a macro so far is below and it works fine except it hides fractions and decimals as well. I would need it to show the row even if the value is 0.3 or 1/3. I am baffled as to why it hides it. Thanks in advance to anyone who can help.

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 6
Const LastRow As Long = 500

'< Set the columns that may contain data >
Const FirstCol As String = "C"
Const LastCol As String = "D"
'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns C to D here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue > 0 Then

'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True
 
Old July 16th, 2010, 10:44 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Try declaring the following

Dim RowRangeValue As Double

also you can convert the values to double before cheching

RowRangeValue = WorksheetFunction.Sum(Val(Range(FirstCol & HiddenRow).Value), Val(Range(LastCol & HiddenRow).Value))

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old July 17th, 2010, 03:07 PM
Registered User
 
Join Date: Jul 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

Thank you, it worked
 
Old August 17th, 2010, 06:07 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

I'd do that a bit differently, myself.

This is how I would code it:
Code:
Public Function SumsOverZero(wsSource As Worksheet, iColNumberToCheck As Long) As Boolean

'Returns True if column contains any values and those values are > 0
  Dim wsf As WorksheetFunction, bIsZero
  Set wsf = Excel.Application.WorksheetFunction
  SumsOverZero = wsf.CountIf(wsSource.Columns(iColNumberToCheck), ">0") > 0

End Function

Public Sub HideZeroColumns()

'Hides columns with no values over 0 for columns A through E
  Dim wsSheet As Worksheet, rWholeRange As Range, rThisCol As Range
  Set wsSheet = ActiveSheet
  Set rWholeRange = wsSheet.Range("A1:G1") 'Use only 1 row to limit to 1 itteration for each column
  For Each rThisCol In rWholeRange
    wsSheet.Columns(rThisCol.Column).Hidden = Not SumsOverZero(wsSheet, rThisCol.Column)
  Next

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Empty rows created in Access table ayazhoda Access VBA 7 May 10th, 2007 06:23 AM
How do I hide Excel rows with zero values-no print LMG VB How-To 0 April 24th, 2007 04:37 PM
populate details view or list view non empty rows iinfoque ASP.NET 2.0 Basics 0 March 11th, 2007 06:11 AM
Hide/show rows in a table smi13y XSLT 6 December 28th, 2006 08:48 PM
Dynamic Table calls 2 empty rows nancy Dreamweaver (all versions) 1 January 18th, 2006 06:00 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.