0

Relatively new to VBA, so apologies if this is a poorly phrased question:

How can I set up a macro so that it will copy the entire row containing the active cell to the next empty row down?

Potential complications:

  • All data is being entered in a pivot table due to other functions
  • One column auto-calculates, does that count as not being empty?
  • I managed to make a macro copy to the next row down, but it will overwrite that row as it stands.

(I know I could copy paste the row but I'm making a very large spreadsheet for other people who want to fill it as fast as possible.)

A brief description of what I have so far:

Sub CopyDown()
'
' CopyDown Macro
' duplicate current row
'
' Keyboard Shortcut: Ctrl+w
'
    If IsEmpty(ActiveCell.Offset(1, 0).EntireRow) Then
    'This part works
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    ActiveSheet.Paste
    'This part works

    End If
End Sub

Advice would be appreciated, even if it's just the right keywords to search for this type of function.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Quill
  • 1
  • Side note: You want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. Doing so will also make your problem easier to solve – cybernetic.nomad Nov 15 '19 at 16:57
  • @cybernetic.nomad , thanks, but the entire purpose of this macro is to copy the entire current row down to the next empty row, I'm not sure how I could avoid using select and still be able to have this work for any row? (All the alternatives I've seen involve setting a range. this is a workbook that will log thousands of queries solved by 7+ different users.) – Quill Nov 20 '19 at 17:10
  • There are several questions here about finding the last row. Did you go to the link I provided? – cybernetic.nomad Nov 20 '19 at 17:18
  • Yes, the problem lies in understanding how I can get around the examples, which list the ranges as things like "A1:B10" when I want the range of cells being copy and pasted to be "A17:X17" when the currently highlighted cell is in row 17, and "A223:X223" when the currently highlighted cell is in row 223. – Quill Nov 20 '19 at 17:40
  • Tried a couple other setups I found: >Dim x as Integer >x = ActiveSheet.UsedRange.Rows.Count >Activecell.SpecialCells(xlLastCell).Select >ActiveCell.EntireRow.Cells(1,1).Offset(1,0).Activate – Quill Nov 20 '19 at 17:41
  • Can't quite copy the other setups I've found, but two examples of "jump to first empty cell" https://excelribbon.tips.net/T006197_Jumping_to_the_Start_of_the_Next_Data_Entry_Row.html And "find the last non-blank cell in column A, offset 1 down" https://www.excelcampus.com/vba/find-last-row-column-cell/ Have both not worked when I substituted them for line 3 of the "this part works" segment. – Quill Nov 20 '19 at 17:46

0 Answers0