PDA

View Full Version : hide the row if the row has nothing or more than 1 cell has things



clarksonneo
06-24-2011, 12:12 PM
Hi,

I need a macro which can perform the following action:

for each row in selection,
if all cells on a row have nothing, then hide the row,
if there are more than 1 cell which are not emtry, then hide the row,
so only not hide the row if the row only has 1 cell which is not emtry.

Could you please write the macro for me?

thanks

p45cal
06-24-2011, 03:29 PM
Sub blah()
For Each rw In Selection.Rows
If Application.WorksheetFunction.CountA(rw) = 1 Then rw.EntireRow.Hidden = False Else rw.EntireRow.Hidden = True
Next rw
End Sub

clarksonneo
06-25-2011, 07:13 AM
Sub blah()
For Each rw In Selection.Rows
If Application.WorksheetFunction.CountA(rw) = 1 Then rw.EntireRow.Hidden = False Else rw.EntireRow.Hidden = True
Next rw
End Sub



Hi,

Your code works, thank you.
can I ask you a further question?

I change your code slightly.
However, it doesn't work.
The code is:

Sub blah()
For Each x In Selection
rw = x.Row
If Application.WorksheetFunction.CountA(rw) = 1 Then
x.EntireRow.Hidden = False
Else
x.EntireRow.Hidden = True
End If
Next
End Sub

Your version: for each row in selection
my version: for each cell in selection, then set rw be the row of the cell

could you please amend the code so that my version become work?
I ask because I want to learn only.

thanks

p45cal
06-25-2011, 10:52 AM
I change your code slightly.
However, it doesn't work. Well that depends on what 'work'ing means. In English, what do you want it to do?


The code is:

Sub blah()
For Each x In Selection
rw = x.Row
If Application.WorksheetFunction.CountA(rw) = 1 Then
x.EntireRow.Hidden = False
Else
x.EntireRow.Hidden = True
End If
Next
End Sub
Your version: for each row in selection
my version: for each cell in selection, then set rw be the row of the cell
In your code rw is a number. CountA is looking for a range.
Since I don't know what you want it to do the following suggestion is a guess; change:
rw = x.Row
to:
Set rw = x.EntireRow

Later in the code change the lines:
x.EntireRow.Hidden = False/True
to:
rw.Hidden = False/True