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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.