How to specify fixed range for data scan

Cowichandave

New Member
Joined
Jan 18, 2009
Messages
10
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,174
{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:
Code:
    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

New Member
Joined
Jan 18, 2009
Messages
10
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
 

Forum statistics

Threads
1,081,425
Messages
5,358,604
Members
400,505
Latest member
JacquiT

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top