Print macro w/ loop

QuestionEdit

Below is a macro that I am using. It works for the most part. The only problem is the I can't get it to stop. I have 2 tabs in 1 workbook: Input and sheet1. I am attempting to update sheet1 with data from the Input tab. Like I said that macros is working except I can't set a specific range to stop the macro. Sheet1 is grabbing data from Input tab column A rows 1-30. Instead of stopping at row 30 the macro continues to the bottom of the spreadsheet. How do I set the range to include only rows 1-30?

Sub PrintData() Dim r As Range, cell As Range With Worksheets("Input") Set r = .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown)) End With For Each cell In r

Worksheets("sheet1").Range("C5").Value = cell.Value
Application.Calculate
Worksheets("sheet1").PrintOut

Next End Sub

AnswerEdit

Steve,

If your data stops at row 30, then

  .Cells(2,"A").End(xldown)

should stop at row 30. Apparently there is something in the cells beyond row 30 that is causing then End(xldown) to go past row 30.

An easy fix is to make it a static range

Sub PrintData() Dim r As Range, cell As Range With Worksheets("Input") Set r = .Range("A2:A30") End With For Each cell In r

 if len(trim(cell.Text)) > 0 then
  Worksheets("sheet1").Range("C5").Value = cell.Value
  Application.Calculate
  Worksheets("sheet1").PrintOut
 End if

Next End Sub

I put in an IF statement so it won't print anything if a cell in column A in the specified range is blank (or looks blank). So you could make the range a bit longer if the end isn't absolutely at row 30 and it will still only print the cells in column A that have data.

Another thing you could do is clean up the sheet below row 30 (delete all the rows). Then select A2 and hit the end key, then the down arrow. Continue to clean it up if it goes past the last cell you believe has data. then save the workbook when it is working correctly. Then you should be able to use your original macro.