Copy data from table and clear specified rows

nickthebizz

New Member
Joined
Jan 24, 2021
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hi I have the following table shown in the image. I am trying to figure out a macro do to so.

I would like to specify the number of rows which this macro should preform once I give the number of rows and press the button.

Example, when i specify 2 rows then I want to delete the data in the last two rows (20 and 21), copy from row 2 until 19 to row 4 until 21. And then clean the data from row 2 and 3 so I can add new data there.

Basically to shift all data from row 2 until 19 two steps down and clean the first two rows for the new data. The last two rows are not needed anymore.

I
 

Attachments

  • Capture.PNG
    Capture.PNG
    40.4 KB · Views: 12

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
There are a number of ways of achieving this. They will all have some issues if there are formulas involved. Formulas within the table will have different issues to formulas accessing data in the table.

Assuming the table has no formulas in it below is one option:-

Note: Merging cells is a really bad idea and should be mostly avoided unless absolutely necessary. I can see no reason for merging columns B & C or even the cells to the right of the table. You are getting a lot of views but few takers, I wonder if it is either the merged cells or the lack of an XL2BB that is contributing to this.

VBA Code:
Sub ShiftCellsDown()
    Dim rng As Range
    Dim sht As Worksheet
    Dim ShiftRows As Long
    
    Set sht = ActiveSheet
    ShiftRows = sht.Range("H3").Value           ' <--- Adjust this to be the cell with the Number of Rows
    
    Set rng = sht.Range("A1").CurrentRegion
    rng.Offset(ShiftRows + 1).Resize(rng.Rows.Count - 1 - ShiftRows) = _
            rng.Offset(1).Resize(rng.Rows.Count - 1 - ShiftRows).Value
    rng.Offset(1).Resize(ShiftRows).ClearContents

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top