Delete and then re-insert rows and columns

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
602
Office Version
  1. 365
Platform
  1. Windows
I have a range of data that is contained within 6 contiguous columns by 29 contiguous rows. It can appear anywhere in a worksheet beginning in column C (would not be in columns A or B) and beginning in row 3 (would not be in rows 1 or 2).

I'd like to run a macro that would
(1) delete all the rows above this range and all the columns to the left of this range so that data appears in range B2:G30
(2) run another macro called "Evaluate"
(3) insert the same number of rows and columns deleted in (1) so that data range is back to where it was originally

Is there a way to do this? Thanks.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Does your range have a name? if not, how do we know where it starts, e.g. is there a particular column header we can search for?

Why do you need to delete rows/columns and then re-insert? Would clearing the row/columns suffice? Or does your code for Evaluate maybe rely on the data range starting in B2? If so, could we rewrite Evaluate to make it more flexible?
 

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
602
Office Version
  1. 365
Platform
  1. Windows
Does your range have a name? if not, how do we know where it starts, e.g. is there a particular column header we can search for?

Why do you need to delete rows/columns and then re-insert? Would clearing the row/columns suffice? Or does your code for Evaluate maybe rely on the data range starting in B2? If so, could we rewrite Evaluate to make it more flexible?
I think I figured it out by naming the range and finding the cell reference of the first cell in the range. I only learned now to do that after I posted. I appreciate your reply - your first question confirms I am going in the right direction.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

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
Top