Hello All,

I am using the following code below to hide rows if cells in the range are
blank. It performs the function I desire better than any other solution I
tried. However, it runs slow (~10 seconds) and a cell in the sheet has to be
clicked to run the code if the blank cells in the range have changed and it
then runs everytime a cell in the sheet is clicked or edited.

Is there a way to speed it up greatly?
and/or
have it automatically update / update on change in the range only?
and/or
not run every time a cell in the sheet is clicked/edited?

I am a novice programmer.

Thank you for any help!

Option Compare Text

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("A18:A98")
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End With
End Sub

To carry what Alok offered a little further:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A18:A98")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
'.... your hide a row code here
MsgBox "In the zone"
Application.EnableEvents = True
End Sub

That will only run the code when a change takes place in A18:A98, should
save you lots of time. Of course, you don't have a real easy way to unhide
hidden rows for future use? But I presume you have a plan or purpose for
doing it this way.

"Alok" wrote:

Since the hiding/unhiding of rows is based on the cell value and the cell
value change always causes a Change event, you should call the code withing
the Worksheet_Change event and not withing Worksheet_SelectionChange event.

Alok

"Aaron" wrote:

Hello All,

I am using the following code below to hide rows if cells in the range are
blank. It performs the function I desire better than any other solution I
tried. However, it runs slow (~10 seconds) and a cell in the sheet has to be
clicked to run the code if the blank cells in the range have changed and it
then runs everytime a cell in the sheet is clicked or edited.

Is there a way to speed it up greatly?
and/or
have it automatically update / update on change in the range only?
and/or
not run every time a cell in the sheet is clicked/edited?

I am a novice programmer.

Thank you for any help!

Option Compare Text

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("A18:A98")
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End With
End Sub

Ok, now we have a new issue to deal with: the cells being changed that we
need to know about are on a different sheet! This complicates things. Until
now I was assuming everything was on the same sheet. A manual change in a
cell on Sheet1 that causes an automatic change of value in a cell on Sheet2
is not going to be detected by Sheet2. We have to watch for the change on
Sheet1 and tell it to take action on Sheet2 based on that change.

I'll have to look at it some more now and come back later with (hopefully) a
solution.

And that solution will take your question below this one into consideration.

"Aaron" wrote:

Ok so I am having trouble identifying the cells in the other sheet where the
Data entry occurs. There are 4 key separate cells not together that
dettermine if the code needs to run. How do I indicate that in the code?

"JLatham" wrote:

We may have a small disconnect here - and so you could possibly ignore the
If Intersect()
...
End If

portion of the code. But to clarify even mo First that code and routine
is 'location' dependent. It has to go into the Worksheet's code area. To
get to that for any given sheet, right-click on the sheet's tab and choose
View Code.

Your original code ran through the process every time you moved into any new
cell either by clicking on it with the mouse or using arrow keys, tab key,
enter key, whatever. That's probably why it was slowing you down so much.

This code is only called when there is an actual value changed on the
worksheet by user input. That may be key he changes by formulas don't
trigger the _Change event.

So what I'd written works this way:
When the user makes a change to ANY cell on the worksheet, then the routine
is called and the first thing it does is ask if that change took place in
cells A18:A98, if it took place somewhere else, then it just exits and does
nothing. But if the USER changed something in cells A18:A98, then it does
whatever you intended it to.

But if I'm guessing right he
1 - the user doesn't change things in A18:A98 -- that's done by formula or
VB code.
2 - Some other cell (or group of cells) actually changes value to trigger
the change in values in A18:A98.

If both of those are correct, just change the reference to "A18:A98" to
whatever address or range is the cell/cells that actually get changed to
affect the values in A18:A98.

Does that help explain things a little better?


"Aaron" wrote:

Thank you for your input. I really need it to update by hiding newly blank
and unhiding newly nonblank rows.

Also it seems that because none of the changes are being entered in the
cells in the range this code causes the overall function not to work. All of
the cells in the range are calculated based on information in multiple other
cells outside of this range and even this sheet. Unless of course I applied
your advice incorrectly, or I am a moron if either is the case you have my
apologies.

I am still be interested in tips that would make it run faster when it does
run.

Happy New Year!

"JLatham" wrote:

To carry what Alok offered a little further:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A18:A98")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
'.... your hide a row code here
MsgBox "In the zone"
Application.EnableEvents = True
End Sub

That will only run the code when a change takes place in A18:A98, should
save you lots of time. Of course, you don't have a real easy way to unhide
hidden rows for future use? But I presume you have a plan or purpose for
doing it this way.

"Alok" wrote:

Since the hiding/unhiding of rows is based on the cell value and the cell
value change always causes a Change event, you should call the code withing
the Worksheet_Change event and not withing Worksheet_SelectionChange event.

Alok

"Aaron" wrote:

Hello All,

I am using the following code below to hide rows if cells in the range are
blank. It performs the function I desire better than any other solution I
tried. However, it runs slow (~10 seconds) and a cell in the sheet has to be
clicked to run the code if the blank cells in the range have changed and it
then runs everytime a cell in the sheet is clicked or edited.

Is there a way to speed it up greatly?
and/or
have it automatically update / update on change in the range only?
and/or
not run every time a cell in the sheet is clicked/edited?

I am a novice programmer.

Thank you for any help!

Option Compare Text

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("A18:A98")
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End With
End Sub

Aaron,
Here's my thinking/logic on this - I'll explain that and then offer up
solution based on it.

The sheet(s) with rows to be hidden/unhidden aren't actually typed into by
the user in any way that affects which rows need to be hidden/unhidden. All
of that is going on in some other sheet(s). If that is true, then while you
are looking at or working with those other sheets, you probably could not
care less which rows are hidden/unhidden on those other sheets - you
certainly cannot see them unless you happen to be working in a split screen
or such. You only need to see the rows in their proper hidden/unhidden state
when you look at that/those sheet(s).

Again, if all of that is true, then why waste time and effort keeping the
status of something you cannot see updated at all? In this case, I'd put my
row hide/unhide code in those sheet's _Activate() event. Then when you click
on that/those sheets to view them, their status would be updated.

The code to go into the worksheet code area - is exactly like what you had
before, just associated with a different event:
Private Sub Worksheet_Activate()

Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In Range("A18:A98")
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End With
Application.ScreenUpdating = True
End Sub

You'll have to duplicate that code in each sheet that has the rows to be
hidden or unhidden.

THE CATCH: There's always a catch. The catch would be when you've made
changes on other sheets that affect how those special sheets should look, but
you don't choose any of them after making those changes and decide to print
the entire workbook, expecting those sheets to have properly hidden/unhidden
rows.

To get around the catch, put this code into the WORKBOOK's _BeforePrint()
event:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet
Dim sheetNames() As String
Dim sheetCount As Integer

If Workbooks(ThisWorkbook.Name).Windows(1).SelectedSh eets.Count 1 Then
MsgBox "more than one sheet selected"
sheetCount = 0
'save names of currently selected sheets
For Each sh In Workbooks(ThisWorkbook.Name).Windows(1).SelectedSh eets
sheetCount = sheetCount + 1
ReDim Preserve sheetNames(1 To sheetCount)
sheetNames(UBound(sheetNames)) = sh.Name
Next
'because now we're going to unselect them to
'make sure rows are properly hidden/unhidden before printing
'by activating each in turn (no check made to see which sheet is which)
For Each sh In Worksheets
sh.Activate ' triggers updating the hidden/unhidden rows
Next
'now reselect them for printing
Worksheets(sheetNames()).Select
End If

End Sub


"Aaron" wrote:

As you can propably tell I'm learning as I go.

Since I have 2 worksheets that recieve data entry and 6 that produce
output(reports) could I place the code in the workbook section of of the VB
and use the following event: Private Sub Worksheet_Activate()
And run the code on each of the worksheets in the workbook?



"JLatham" wrote:

We may have a small disconnect here - and so you could possibly ignore the
If Intersect()
...
End If

portion of the code. But to clarify even mo First that code and routine
is 'location' dependent. It has to go into the Worksheet's code area. To
get to that for any given sheet, right-click on the sheet's tab and choose
View Code.

Your original code ran through the process every time you moved into any new
cell either by clicking on it with the mouse or using arrow keys, tab key,
enter key, whatever. That's probably why it was slowing you down so much.

This code is only called when there is an actual value changed on the
worksheet by user input. That may be key he changes by formulas don't
trigger the _Change event.

So what I'd written works this way:
When the user makes a change to ANY cell on the worksheet, then the routine
is called and the first thing it does is ask if that change took place in
cells A18:A98, if it took place somewhere else, then it just exits and does
nothing. But if the USER changed something in cells A18:A98, then it does
whatever you intended it to.

But if I'm guessing right he
1 - the user doesn't change things in A18:A98 -- that's done by formula or
VB code.
2 - Some other cell (or group of cells) actually changes value to trigger
the change in values in A18:A98.

If both of those are correct, just change the reference to "A18:A98" to
whatever address or range is the cell/cells that actually get changed to
affect the values in A18:A98.

Does that help explain things a little better?


"Aaron" wrote:

Thank you for your input. I really need it to update by hiding newly blank
and unhiding newly nonblank rows.

Also it seems that because none of the changes are being entered in the
cells in the range this code causes the overall function not to work. All of
the cells in the range are calculated based on information in multiple other
cells outside of this range and even this sheet. Unless of course I applied
your advice incorrectly, or I am a moron if either is the case you have my
apologies.

I am still be interested in tips that would make it run faster when it does
run.

Happy New Year!

"JLatham" wrote:

To carry what Alok offered a little further:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A18:A98")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
'.... your hide a row code here
MsgBox "In the zone"
Application.EnableEvents = True
End Sub

That will only run the code when a change takes place in A18:A98, should
save you lots of time. Of course, you don't have a real easy way to unhide
hidden rows for future use? But I presume you have a plan or purpose for
doing it this way.

"Alok" wrote:

Since the hiding/unhiding of rows is based on the cell value and the cell
value change always causes a Change event, you should call the code withing
the Worksheet_Change event and not withing Worksheet_SelectionChange event.

Alok

"Aaron" wrote:

Hello All,

I am using the following code below to hide rows if cells in the range are
blank. It performs the function I desire better than any other solution I
tried. However, it runs slow (~10 seconds) and a cell in the sheet has to be
clicked to run the code if the blank cells in the range have changed and it
then runs everytime a cell in the sheet is clicked or edited.

Is there a way to speed it up greatly?
and/or
have it automatically update / update on change in the range only?
and/or
not run every time a cell in the sheet is clicked/edited?

I am a novice programmer.

Thank you for any help!

Option Compare Text

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("A18:A98")
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End With
End Sub