Hide and Unhide rows based on the condition set

[nenadmail]

Hello,

I am trying to use the code provided on:
vbaexpress.com/kb/getarticle.php?kb_id=512

It is set to check the worksheet for entry, if no entry in a range of cells, then the entire row hides. Otherwise, it unhides.

And it works fine except for two things....
1. First, it brings up an error when I set the range to "A" only

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

It only works when I set the range to have at least 2 columns, like this:

'< Set the columns that may contain data >
Const FirstCol As String = "A"
Const LastCol As String = "B"



2. And secondly, it only shows rows with numbers and hides rows with text. But I have both numbers and text. I'm a layman to coding... So, can you please send me a new code that will work for me?

Thank you very much!

[Bob Phillips]

Show us the code that you have now, that throws the error.

[nenadmail]

HERE IS THE CODE:

The error only comes up when the range is "A" and "A"... but that's the only column that I need it to look at.

The other problem is: It also hides TEXT, but I need it to unhide any row with text or numbers.





Option Explicit

Private Sub Worksheet_Activate()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

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

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

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns B to G 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

End Sub

[Teeroy]

Text in the range has a numeric value of 0 and therefore Application.Sum=0 which doesn't trigger the If statement. Try using Application.Count <>0 instead which counts non-empty cells.

[Bob Phillips]

You can also simplify


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

to


Rows(HiddenRow).EntireRow.Hidden = RowRangeValue = 0