how to prevent value from active cell from changing

Question

The code here reorders from one sheet range to match the order in a pivit in another sheet.

the issue is that the value "first" is set to activecell and seems to change, and the range changes as result. how can I keep the value "first" from changing?


Sub Reorder()
'
' Macro8 Macro
'

'

Dim First As Range
Dim Range1 As Range
Dim Range2 As Range
Dim lRow As Long
Dim Count As Long
Dim Net As String
Dim Line As Range
Dim Netrng As Range
Dim Swith As Range

    If ActiveCell.Column = 2 Then
    Set First = ActiveCell
    Set Range1 = Range(First, First.End(xlDown))
    ActiveWindow.ActivatePrevious

    ActiveSheet.Range("B5").Activate
    lRow = Cells(Rows.Count, 1).End(xlUp).row - 6
    Set Range2 = Range(ActiveCell.Offset(3, -1), ActiveCell.Offset(lRow, -1))


    Count = 1
      While Count <= Range2.Count
      Set Line = Range2.Rows(Count)
      Net = Line.Value


      ActiveWindow.ActivatePrevious

      Range1.Select
      Set Netrng = Range1.Find(What:=Net, After:=ActiveCell, LookIn:=xlValues, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)


      If Not Netrng Is Nothing And Netrng.row <> Range1.Rows(Count).row Then
       Rows(Netrng.row & ":" & Netrng.row).Select
       Selection.Cut
       Rows(Range1.Rows(Count).row & ":" & Range1.Rows(Count).row).Select
       Selection.Insert Shift:=xlDown
      End If
      ActiveWindow.ActivatePrevious
      Count = Count + 1
      Wend
 Else
MsgBox "Please select correct column"
End If



End Sub

[This may be useful](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Jan 16 '20 at 20:09
1) It tell syou why you don't want to use `Select` and `Activate` in your code 2) It provides several strategies to do so, such as using a variable for the range, which you may find solves your problem — cybernetic.nomad, Jan 16 '20 at 20:14

score 0 · Answer 1 · answered Jan 16 '20 at 20:21

0

I figured out how to make it work with these changes...

Dim First As String
First = ActiveCell.Address
Set Range1 = Range(First, Range(First).End(xlDown))

answered Jan 16 '20 at 20:21

Goldaaron18

11
5

how to prevent value from active cell from changing

1 Answers1