How do I move cell to another area when scroll is locked

Cowichandave

New Member
Joined
Jan 18, 2009
Messages
44
Office Version
  1. 2016
This is a worksheet with 2 forms Columns A:J and Columns K:T

Data is entered in rows 7 to 55 and user is prevented from scrolling outside this area. How do I modify the following script. At present it allows me to search and enter data in the first blank row up to row 55. I would like it to detect when row 55 is full and then unlock the scroll area and move to columns K:T and relock the scroll area for those columns. This is a 2 sided form that is printed

Private Sub Worksheet_Activate()
For irow = 7 To 55
If Application.CountBlank(Cells(irow, 1).Resize(1, 10)) = 10 Then
Cells(irow, 1).Select
Exit For
End If
Next
ActiveSheet.ScrollArea = "A1:J60"

End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,215,032
Messages
6,122,772
Members
449,095
Latest member
m_smith_solihull

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top