Select cells, insert new row, move rest of table down

**Bob_James** · 06-03-2015, 10:12 PM

I have two tables side by side. Each table has four columns. One of the tables contains more data rows than the other (but the rest of its data is identical to the short one). I need to insert rows just in the short table to move data rows down so the data in the short table matches the data in the long table.

It's easy enough to do "by hand": highlight the four cells that need to be "pushed down", press insert, and select "shift down".

I want to do this by selecting just the first cell in the short table row and running a macro to do the rest.
I am unable to create the correct code to make the four cell selection.

This is the code obtained by manually creating a macro:

'Select the Range
Range("E187:H187").Select
'Insert and move the rest down
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

What I need is the correct code to replace the selection (line 2) so that it is generic (works in any row)

Thank you for this opportunity to obtain help

**sktneer** · 06-03-2015, 10:33 PM

Hi Bob,
Welcome to the forum.

You may try something like this....

Please Login or Register  to view this content.

**Bob_James** · 06-04-2015, 12:02 AM

Thank you for your prompt reply!!

Your code works perfectly (of course), but what I was trying to do was eliminate the need to highlight (or in this case, enter) the additional cells. Being lazy, I just wanted to click on the first cell and use shortcut keys to finish the process of selecting the other three cells (and do the entering and moving).

Many years ago I did a lot of programming in Basic, Fortran IV, dBase and Lotus Script and it was easy to "tell" the program to move right 5 cells and down 3, select the 3 cells to the right, etc. I was hoping there was an equally easy way to do that in VB.

Thank you again for your reply

**sktneer** · 06-04-2015, 12:38 AM

Okay. Then try this....

Please Login or Register  to view this content.

Press Alt+F8 to open Macro window --> Select InsertRows from the available macros --> Options --> Set the shortcut key as per your choice --> OK

Now after selecting a single cell and hitting the shortcut key will do the job for you.

**Bob_James** · 06-04-2015, 05:39 AM

So
ActiveCell.Resize(1, 4)
is the code to increase the selection

Super. Thank you

**sktneer** · 06-04-2015, 05:42 AM

You're welcome Bob!

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

Select cells, insert new row, move rest of table down

LinkBack

Thread Tools

Rate This Thread

Display

Select cells, insert new row, move rest of table down

Re: Select cells, insert new row, move rest of table down

Re: Select cells, insert new row, move rest of table down

Re: Select cells, insert new row, move rest of table down

Re: Select cells, insert new row, move rest of table down

Re: Select cells, insert new row, move rest of table down

Thread Information

Users Browsing this Thread

Similar Threads

Is there a way to seperate the table from the rest of the cells?

Need hyperlink to move to another worksheet with rest of the row

Macro : To find word and Select rest after this cell and delete rest

[SOLVED] Cells that move with the rest of the worksheet

Select range of cells and move

Bookmarks

Bookmarks

Posting Permissions