+ Reply to Thread
Results 1 to 5 of 5

Ending Macro - Variable Range

Hybrid View

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    operation
    MS-Off Ver
    Excel 2010
    Posts
    15

    Ending Macro - Variable Range

    The following macro pulls calculated data from another worksheet and then performs a calculation based on that copied over data, then moves down a row and repeats. It is currently set-up to go down 300 rows. I want it to stop after it hits an empty cell in column C, but have not been able to figure it out. Any help is appreciated.

    Sub Backlog_Update()
    
    '
    ' Backlog_Correction Macro
    ' Backlog Copy from International File.
    '
    
    '
    Dim startRow As Integer
    startRow = 2
    endRow = 300
    
    Do
        Windows("Daily_DSI_Report.xlsm").Activate
        Sheets("LCD").Select
        Range("A" & startRow).Select
        Selection.Copy
        Windows("DASHBOARDS-REPORT.XLS").Activate
        Range("T1").Select
        ActiveSheet.Paste
        Range("V2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Daily_DSI_Report.xlsm").Activate
        Range("AG" & startRow).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("B" & startRow).Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[1]-RC[31]<0,0,(RC[1]-RC[31])),0)"
        Range("A" & startRow).Select
        
        startRow = startRow + 1
        
    Loop Until startRow > endRow
    End Sub

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Ending Macro - Variable Range

    Try something like

    endRow = Cells(Rows.Count,1).End(xlup).Row
    to work out what the last row is (in this case based upon column 1)
    Martin

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    operation
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Ending Macro - Variable Range

    Thank you for the amazingly quick response! How do I incorporate that into the macro? I tried replacing "endRow= 300" with your recommendation, and it just keeps going. I tried it with changing (Rows.Count,1) to (Rows.Count,3) thinking it would key on column C, but it just kept going. I tried a couple of Different "Loop Untils" but no luck. I'm still very green with macros, but I'll get there...

  4. #4
    Registered User
    Join Date
    07-16-2013
    Location
    operation
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Ending Macro - Variable Range

    Check that.... I can make the macro work. Currently I have this as the third macro to be run in a set. The first one copies over the base data, removes duplicates and sorts. The second one just sets up the formula on the other worksheet, and then this one. If I run this one by itself, it works and stops at the right place. When I try and run the set, it just keeps going. What causes that?

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Ending Macro - Variable Range

    Difficult to tell without seeing the workbook itself. You might try putting a breakpoint on the new line of code and hover over the endRow variable with your mouse - do you get the correct line number. If not, the right sheet might not be active at the point that you have the line.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. excel 2007 macro need to have variable range of rows rather than fixed range
    By JW1028 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2013, 02:45 PM
  2. VBA Select mutiple rows (ending range variable)
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-30-2011, 04:16 PM
  3. determine begining row,col and ending row,col of variable range
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-17-2011, 08:09 PM
  4. autofill in macro range is constant- how can I code to be a variable range?
    By mcg7@ntrs.com in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-20-2010, 12:44 AM
  5. generating list of names ending by a variable string
    By DaSpunkyGenius in forum Excel General
    Replies: 2
    Last Post: 10-24-2006, 03:04 AM

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