Excel VBA - Scroll to first row under freeze frame

Question

I have a table which I fill via VBA code and SQL. There is a freeze from at row 3 so that the table header doesn't scroll. After loading the data, my table is scrolled to the end of the table. How do I scroll back up to the first row of the table below the header/freeze frame with VBA?

I googled this: "vba get current cell freeze row" and I got this: `Application.Goto Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn), 1` when Freeze is added it effectively changes the ScrollRow from 1 to the one where the Freeze is, that VBA code now would point to that first Scroll point. Edit: But it seems to work on some cases not all. In the same result i got from google there are other suggestions that seem to work. I cant copy paste them in here. — Ricardo A, Mar 17 '23 at 17:30

score 1 · Accepted Answer · answered Mar 17 '23 at 18:32

Public Sub ScrolToRowColumn(ByVal rw As Integer, ByVal cl As Integer)
   If Not (ActiveWindow Is Nothing) Then
      With ActiveWindow
         If .Split Then
            rw = IIf(rw > .SplitRow + 1, rw, .SplitRow + 1)
            cl = IIf(cl > .SplitColumn + 1, cl, .SplitColumn + 1)
         End If
         If (.ScrollRow <> rw And rw > 0) Then
            .ScrollRow = rw
         End If
         If (.ScrollColumn <> cl And cl > 0) Then
            .ScrollColumn = cl
         End If
      End With
   End If
End Sub

Public Sub scrollToCell(c As Variant)
   Dim r As Range, tname As String
   tname = TypeName(c)
   If tname = "Range" Then
      Set r = c.Cells(1, 1)
   ElseIf tname = "String" Then
      Set r = ActiveSheet.Range(c).Cells(1, 1)
   End If
   Call ScrolToRowColumn(r.Row, r.Column)
End Sub


Sub exampleA()
   'if call with rw 0 does not scroll row
   'if call with cl 0 does not scroll column
   
   Call ScrolToRowColumn(4, 0)   'scroll only row
   Call ScrolToRowColumn(0, 3)   'scroll only column
   Call ScrolToRowColumn(4, 3)   'scroll column and row
   Call scrollToCell("C4")      'call with the name of Activesheet range
   'Call scrollToCell(MYSHEET.Range("C4"))  'call with a sheet's range
End Sub

Excel VBA - Scroll to first row under freeze frame

1 Answers1