Results 1 to 5 of 5

Copy or Move rows starting with a particular cell value until next blank row

Threaded View

sborda Copy or Move rows starting... 11-27-2012, 04:58 PM
arlu1201 Re: Copy or Move rows... 12-04-2012, 08:23 AM
sborda Re: Copy or Move rows... 12-04-2012, 12:57 PM
sborda Re: Copy or Move rows... 12-04-2012, 05:36 PM
arlu1201 Re: Copy or Move rows... 12-05-2012, 07:53 AM
  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    California
    MS-Off Ver
    Excel 2011
    Posts
    2

    Question Copy or Move rows starting with a particular cell value until next blank row

    Hi-
    I need a macro to loop through the worksheet rows starting at a row with cell value of: "Depth (m)" and ending at the next blank row.

    There is text and blank rows before the "Depth (m)" then a series of data then a blank row then more text and what I want is data in between to be copied or moved to a new sheet w/in the workbook.

    Here is a sample of the data:
    ------------------------------------------
    Big Lake
    27 November, 2012
    Jane & Jime
    All American City, UT (USA)

    OLD meter (#00314)
    North
    Depth (m) T (deg-C)
    0 31.21
    1 32.64
    2 34.70
    3 36.76
    4 36.92
    5 36.92
    6 36.12
    7 35.47
    8 35.05
    9 34.32
    10 33.96
    11 33.72
    12 33.54
    13 33.09
    14 32.78
    15 32.43
    16 32.79
    17 33.02
    18 33.13
    19 33.23
    20 32.68
    21 31.95
    22 31.51
    23 31.23
    24 31.00

    Doe (Jane, corrected):
    N: 8.0m
    S: 8.0m
    ----------------------------------------------

    This is my code so far:
    ------------------------------
    Sub CopyDepth()
       Dim rownum As Long
       
       Dim startrow As Long
       Dim endrow As Long
       Dim lastrow As Long
       rownum = 1
       colnum = 1
       lastrow = Worksheets("Profile").Range("A65536").End(xlUp).Row
       With ActiveWorkbook.Worksheets("Profile").Range("a1:a" & lastrow)
    
    
       For rownum = 1 To lastrow
        Do
           If .Cells(rownum, 1).Value = "Depth (m)" Then
              startrow = rownum
           End If
    
           rownum = rownum + 1
    
    
       If (rownum > lastrow) Then Exit For
    
       Loop Until .Cells(rownum, 1).Value = 0
       endrow = rownum
       rownum = rownum + 1
    
       Worksheets("Profile").Range(startrow & ":" & endrow).Copy
    
    
       Sheets("data").Select
       Range("A1").Select
       ActiveSheet.Paste
    
    
       Next rownum
       End With
       End Sub
    ----------------------------------------
    But I get this error:
    Run-time error '1004':
    Application-defined or object-defined error

    ----------------
    It works if change this line:
    Loop Until .Cells(rownum, 1).Value = 0

    to this:
    Loop Until .Cells(rownum, 1).Value = "Doe (Jane, corrected):"

    I have been picking at this for a while and can't seem to get it to work.
    Any advice would be great thank you!
    -susan

    Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.
    Last edited by arlu1201; 12-04-2012 at 05:30 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1