What I have is some data in Column A, from rows 1 to some unknown end row. I have some data in row 2 Column T through AC that I want to copy into any open cells in those same rows, and then stop pasting when looking at Row A and seeing the first blank row.
I have the code to copy, find the next open row in T and paste once. What I need is to figure out how to loop until Row A has the first blank cell.
Sub Find_next_BLANK_A_andPaste() ' ' Find_next_BLANK_A_andPaste Macro ' Range("T2:AC2").Select Selection.Copy ' Copy the formulas in T2 to AC2 Range("T1").End(xlDown).Offset(1, 0).Select ActiveSheet.Paste ' End Sub
I usually reference cells in VBA using the cells property with index numbers, so that's how I have it in the sub below. I'm sure the code below could be modified to use
A1 notation instead. But here's how I would do it:
Sub Find_next_BLANK_A_andPaste() Dim CurrentRow As Long Dim CurrentWS As Worksheet 'Set the current worksheet as a variable just so it can be referenced 'easier. If your worksheet isn't the first worksheet of the workbook, 'you'll need to replace Worksheets(1) below with the proper reference 'to your sheet (i.e. Worksheets(2) or Worksheets("Sheet3")) Set CurrentWS = ActiveWorkbook.Worksheets(1) With CurrentWS 'Start at row 1 CurrentRow = 1 'Loop until the cell in column A of the next row is blank While .Cells(CurrentRow + 1, 1) <> "" 'Copy everything from column T to column AC of the current row .Range(.Cells(CurrentRow, 20), .Cells(CurrentRow, 29)).Copy 'Select column T of the next row .Cells(CurrentRow + 1, 20).Select 'Paste the copied data ActiveSheet.Paste 'Increment the CurrentRow variable to check the next row CurrentRow = CurrentRow + 1 Wend End With End Sub
This assumes the first blank cell we find in column A is the "end". If there could be blank cells in column A before we've actually reached the end of the data in column A we'll have to modify the above code a little.