Forum Discussion

Beers Team's avatar
Beers Team
Copper Contributor
Jul 24, 2018

Need Macro that inserts Partial Row into excel (Not an entire Row)

Hi all,

 

I have a version of our team's spreadsheet attached here.

 

I've learned how to use a macro to add a new row, but the problem is, it adds an ENTIRE row and i only want to insert a row between columns "M" and "AF" (on the "Pending" half of the sheet) 

 

Is this possible? Can someone help me?

 

Thanks so much!

developer
excel
Excel Online
Formulas and Functions
Macros and VBA
office 365
  • Tanya Armitage's avatar
    Tanya Armitage
    Copper Contributor
    Jul 24, 2018

    You can highlight where you want the row to be inserted, right click and select insert then choose shift cells down. 

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Jul 25, 2018

    if there are data after the designated row to insert -  you might have to copy down one cell those data.

    example: row to insert = row (M)40

     LastRow = Cells(Rows.Count, "M").End(xlUp).Row

     range("M40:AF" & Lastrow).copy

    range("M41").select

    ActiveSheet.paste

    range("M40:AF40").ClearContents

     

    I didn't use CUT - as sometime queer things happens

    try this out in a different workbook - just in case.

     

    HTH

       

    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Jul 25, 2018
      a shorter version:
      LastRow = Cells(Rows.Count, "M").End(xlUp).Row
      Range("M40:AF" & Lastrow).copy Range("M41")
      Range("M40:AF40").ClearContents


      or you may try:
      LastRow = Cells(Rows.Count, "M").End(xlUp).Row
      Range("M40:AF" & Lastrow).Cut Range("M41")

Resources