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