How to specify fixed range for data scan

[Cowichandave]

New to Excel

I am using a worksheet that has a designed form of 2 pages side by side formatted in page layout mode. Page 1 uses columns B:J, Page 2 uses columns L:T. Data is entered in only rows 7 - 55. Page 1 data area B7:J55 Page 2 data area L7:T55. Line 58 contains sum totals of each column.
Column A and column K contain text. This is a 2 sided form that is printed out so that is why it is on one worksheet. This method just causes less confusion.

Problem is that each row will have data in ANY column randomly. eg :C7,G8,A9 etc. I am wanting the worksheet to open to the first blank row between A7:J55 and go to the first blank cell in column B. How do I modify this script to achieve that?

Rows(Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Select

What happens is that this sees fixed text in column K and then moves to line 59 which is off the page.

Any help would be appreciated

Dave

 

[GlennUK]

{snip}
Rows(Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Select

What happens is that this sees fixed text in column K and then moves to line 59 which is off the page.

... no, that's not what is happening. It sees the last non-blank cell in column B, and selects the row after that.

If you are wanting to find when all columns A:J are blank? If so, that has no resemblance to your current code. If that's the case, I'd try something like this:

    For irow = 7 To 55
        If Application.CountBlank(Cells(irow, 1).Resize(1, 10)) = 10 Then
            Cells(irow, 2).Select
            Exit For
        End If
    Next

 

[Cowichandave]

Awesome, works great. I have lots to learn. Another question, when the script detects that this sheet is full at row 55 how would you instruct it to move to column k7 and start again?

Dave