VBA how to pass integer to range values

Question

I'm trying to simplify this function from the following

Public Sub freezeRow(sheetAct, sheetType As String, sheetRow As Integer)

sheetAct.Activate    'Activate sheet
ActiveWindow.FreezePanes = False 'Unfreeze all rows

Select Case sheetType
    Case "Sum"
        'Freeze the Summary spread sheet after row 9
        sheetAct.Range("10:10").Select

    Case "Data"
        'Freeze the Summary spread sheet after row 2
        sheetAct.Range("3:3").Select
End Select

ActiveWindow.FreezePanes = True 'Freeze selected rows

End Sub

to something like this

Public Sub freezeRow(sheetAct, sheetType As String, sheetRow As Integer)

sheetAct.Activate    'Activate sheet
ActiveWindow.FreezePanes = False 'Unfreeze all rows

sheetAct.Range("sheetRow:sheetRow").Select


ActiveWindow.FreezePanes = True 'Freeze selected rows

End Sub

I can't seem to remove the quotes in the range and when I run it tosses some obscure error

`"sheetRow:sheetRow"` should be `sheetRow & ":" & sheetRow"` — Scott Craner, Mar 15 '16 at 17:27
Maybe `sheetAct.Cells(sheetRow, 1).EntireRow.Select`. Also - `sheetRow As Integer` - Integer will error after row 28000 something or other... use `sheetRow as Long` — Darren Bartrup-Cook, Mar 15 '16 at 17:30
Thank you, I'm sure I'll make more rookie mistakes....new to VB — Rick FlyFish, Mar 15 '16 at 17:54

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

I like to use Cells() all the time, since it's more explicit (to me anyways)

sheetAct.Range("sheetRow:sheetRow").Select

with

With sheetAct
    .Range(.Cells(sheetRow,1),.Cells(sheetrow,1)).EntireRow.Select
End with

Or, just use sheetAct.Range(sheetRow & ":" & sheetRow).Select

But why use .Select? You can work around using .Select which is best practice. (Of course, if you're using it to double check you have the right range, then that's fine. It's when you work with the data you want to be careful).

edited May 23 '17 at 12:31

Community

1
1

answered Mar 15 '16 at 17:33

BruceWayne

22,923
15
65
110

The .Cells gives an unqualified reference error sheetAct.Range(.Cells(sheetRow, 1), .Cells(sheetRow, 1)).EntireRow.Select – Rick FlyFish Mar 15 '16 at 17:58
@RickFlyFish - How is `sheetAct` sent to the sub? I'm assuming it's a `Worksheet`. Also note the `.Range` does not start with `sheetAct.` using `With`, just use the `.` as a "place holder". – BruceWayne Mar 15 '16 at 19:18

score 1 · Answer 2 · answered Mar 15 '16 at 18:33

As a quick solution, I suggest something like this:

Public Sub freezeRow(sheetAct As Worksheet, sheetRow As String)
  Application.Goto sheetAct.Rows(sheetRow)
  ActiveWindow.FreezePanes = False
  ActiveWindow.FreezePanes = True
End Sub

You may add the line ActiveWindow.Split = False between the ActiveWindow.FreezePanes = False and ActiveWindow.FreezePanes = True, because if the FreezePanes was done after a Split, then ActiveWindow.FreezePanes = False will change the window back to the Split-state as it was before. In that case ActiveWindow.FreezePanes = True will ignore the selected range and freeze it at the point where the Split is located.

Also I suggest using sheetAct.Rows(sheetRow + 1) because the panes will be freezed over sheetRow. But that is just for a better logic -> having 3 will freeze 3 rows instead if 2.

score 0 · Answer 3 · answered Mar 15 '16 at 18:08

I found that this method worked perfectly

Public Sub freezeRow(sheetAct, sheetRow As String)
  Dim rangeVal As String

   rangeVal = sheetRow & ":" & sheetRow
   sheetAct.Activate    'Activate sheet
   ActiveWindow.FreezePanes = False 'Unfreeze all rows
   sheetAct.Range(rangeVal).Select 'Select appropriate row

   ActiveWindow.FreezePanes = True 'Freeze selected rows

End Sub

This only for freezing Headers are visible while scrolling through the data

VBA how to pass integer to range values

3 Answers3