select question

mtawheed

New Member
Joined
Mar 24, 2005
Messages
16
I have the following macro, works great in my sheet, but you may notice that it hides certain rows based on a calculated value, the problem is that the selected cells after the macro is executed are the hidden or the displayed cells, I don't want this to hapen, I want the selecte cell to be the one that I'm changing, not to jump to the hidden rows... hope you got me

Private Sub Worksheet_Calculate()

If Range("L73").Value > Range("o55").Value Then
Application.EnableEvents = False
With Range("AI3:AI72")
.Value = Range("AF3:AF72").Value
.Sort Key1:=Range("AI3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Sheets("DM1").Rows("75:85").Select
Selection.EntireRow.Hidden = False
Application.EnableEvents = True
Else
Application.EnableEvents = False
Sheets("DM1").Rows("75:85").Select
Selection.EntireRow.Hidden = True
Application.EnableEvents = True
End If
End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Your description is not that clear, ( as you contradict yourself : "the selected cells ... are the hidden or the displayed cells", well, you can't have it both ways! ), but looking at your code instead I notice that you select certain rows to be hidden ... change your code to hide the rows without selecting them:
Sheets("DM1").Rows("75:85").EntireRow.Hidden = False
 

mtawheed

New Member
Joined
Mar 24, 2005
Messages
16
thank you, and sorry for not clarifying, you got what I really needed, many thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,826
Messages
5,574,530
Members
412,601
Latest member
TheBeaniacExpress
Top