PDA

View Full Version : Solved: Preventing undesired jumping of mark



Rejje
12-17-2010, 02:01 AM
Hi!

I have an urgent problem. After any change in a worksheet some subs run that check different ranges/makes changes to cells etc. This makes the marked range become one of these instead of the last cells that was changed/marked. I want the last marked cell to stay marked after these subs have run.

How do I prevent this from happening?
Can code that goes to ranges mark these "silently"?
Or, can one insert some code that let the mark jump back to the last manually marked cell?

Bob Phillips
12-17-2010, 02:38 AM
Give an example Rejje, I am not sure I fully understand.

Rejje
12-17-2010, 03:36 AM
Give an example Rejje, I am not sure I fully understand.

Hi xld! This is an example.

When I make a change to a cell, lets say A1, below subs run.

Public Sub Worksheet_Change(ByVal Target As Range)
Call HideColumns
Call FontSize
Call HideRows
End Sub

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
If Range("V_41200") = False Then

Range(Range("V_41400").Value).Select
Selection.EntireRow.Hidden = True

Else

Range(Range("V_41400").Value).Select
Selection.EntireRow.Hidden = False

End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub HideRows is the last to run which leaves marked cell not to be A1 but an area named V_41400 (which could be either hidden or unhidden).

I would like for cell A1 to still be the marked cell after above code has run. How do I do it?

macropod
12-17-2010, 03:46 AM
Hi Rejjie,

The problem is that you're using Selections. You can do the same processing without changing the selections via:
Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
If Range("V_41200") = False Then
Range("V_41400").EntireRow.Hidden = True
Else
Range("V_41400").EntireRow.Hidden = False
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Rejje
12-17-2010, 04:57 AM
Hi Macropod!

Works perfectly! I just had to change back to Range(Range("V_41400").Value).EntireRow.Hidden as cell V_41400 contains the name of the range of which to hide rows (it's like a "to-do-list" which points to different ranges).

Thanks!

Rejje
12-17-2010, 05:05 AM
Follow-up question since I just discovered this sub makes the problem stay:

How do I modify this code not to select as well? I've tried different ways trying to learn from above solution but I'm still a vba newbie...

Sub FontSize()

Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each cell In Range("RADERA_MALL")

Range(cell.Value).Select

With Selection.Font
.Size = 10
End With

Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

macropod
12-17-2010, 05:18 AM
Hi Rejjie,

There is rarely any need to select or activate a cell. Try:
Sub FontSize()
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each cell In Range("RADERA_MALL")
Range(cell.Value).Font.Size = 10
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Rejje
12-17-2010, 05:26 AM
Problem solved and thread closed!

Yes, I see why these matters shouldn't incorporate any selections and I will not use select unless necessary from now on.

A thousand thanks!