Can macro adjust for adding Columns/rows ??

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
199
I have the following macro, which does what I need it to do, copy data from one column to another.

Range("T31:T549").Select
Selection.Copy
Range("E31").Select
ActiveSheet.Paste
Application.CutCopyMode = False


This macro now copies the data from row T31 to row T549 over to E31 to E549. These are all the data rows currently in the Worksheet. Can I adapt the macro to allow for new rows being added at row 31, which will push old row 31 down to row 32, and old row 549 down to 550 etc?? Rows will not be added above row 31, nor will rows be added below what is the current row 549.


This macro now copies data from Column T to Column E.
Is there a way to adapt this macro so that if columns are added or deleated before Column T that the data will still be copied correctly -- that is from what was originally Column T to the column that was originally Column E ?? Again the data to be copied will be between rows 31 and 549

If the Excel board wizards do NOT think that doing this is possible I would appreciate hearing that as well, so that I don't keep asking this same question.


Thanks

StanSz
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hello, StanSz,

you can code like this
(one line instead of 5 :) )
Code:
Range("T31:T549").Copy Range("E31")
to solve your problem, I think you could use named ranges
name T31: FirstCell
name T549: LastCell
name E31: TargetCell
then the code would be
Code:
Range("FirstCell:LastCell").Copy Range("TargetCell")
of course don't delete these cells (or the columns with those cells)
does this help ?

kind regards,
Erik
 
Upvote 0
Thanks Erik,

Yes, one line instead of five is helpful, but how does this address the problem of my inserting a new row at row 31, which bumps down all the other rows and thus, the 'Last Cell' would be T550 instead of T549 ??

And what if a column is added before Column D, then how would the macro know that it should copy from Column U (old column T) to Column F (old column E) ??

Thanks

StanSz
 
Upvote 0
This only works if you have Nothing in row 30 except for headers above your Data Column and Target Column.

Sub testy()
Range("IV30").Select
Selection.End(xlToLeft).Offset(1).Select
Range(Selection, Selection.Offset(518)).Copy
ActiveCell.Offset(-1).Select
Selection.End(xlToLeft).Offset(1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub




If there is other data or headers in row 30 then you could search row 30 for the headers you are after... but I can't accurately help you with that. :oops:
 
Upvote 0
StanSz,
when you wrote
Yes, one line instead of five is helpful, but how does this address the problem of my inserting a new row at row 31, which bumps down all the other rows and thus, the 'Last Cell' would be T550 instead of T549 ??

And what if a column is added before Column D, then how would the macro know that it should copy from Column U (old column T) to Column F (old column E) ??
did you try out my suggestion ?
click in T31
click in namebox
type FirstCell
enter
insert a line or column
where is the range FirstCell now ?

as far as i understood your problem, this will solve it, since the named range is moving around when inserting columns or rows

best regards,
Erik
 
Upvote 0
Erik,

Now I understand. I thought you were directing me to name the ranges within my VBA macro code, and I was going nowhere. You were directing me to name the cells on the Worksheet itself

Yes, FirstCell and LastCell do move around when I insert or delete rows and columns --- wonderful.

Since I'll always be wanting to include my new inserted row at row 31 in the copy, I'll just name T30 as FirstCell, and E30 as TargetCell and then my adaptation of your VBA macro code will be:

Range("FirstCell+1:LastCell").Copy Range("TargetCell+1") and this will copy from T31 down to my Last Cell.

Thanks for the help, and sorry for not immediately understanding your simple and elegant solution.

StanSz
 
Upvote 0
Thanks for the help, and sorry for not immediately understanding your simple and elegant solution.
you're really very welcome :)
I never think people don't understand "deliberately" the explanations.
named range is "sheetlanguage"
defined name is "VBAlanguage" I think

kind regards,
Erik
 
Upvote 0
Erik,

One more wrinkle:

The one line of code:
Range("FirstCell:LastCell").Copy Range("TargetCell")
works well, but copies over the formulas from the FirstCell:Last Cell Range.

I actually want to copy over just the values (I'm doing this to avoid a circular reference problem.)

I've tried the following code, but I'm getting errors, and don't know enough how to fix it myself:

Range("FirstCell:LastCell").PasteSpecial (xlPasteValues), Range("TargetCell")

Thanks

StanSz

P.S. My "FirstCell+1" thought works as a formula on the ws, but it doesn't work in a macro
 
Upvote 0
you can find the syntax for your PasteSpecial using the macro recorder: then edit to your ranges

or use this syntax, which I like
(assuming Range("FirstCell:LastCell") is only one column
Code:
mem = Range("FirstCell:LastCell").Value
Range("TargetCell").Resize(Range("FirstCell:LastCell").Rows.Count, 1) = mem
more "professional"
Code:
Dim rng As Range
Set rng = Range("FirstCell:LastCell")
    With rng
    mem = .Value
    Range("TargetCell").Resize(.Rows.Count, .Columns.Count) = mem
    End With
not tested and didn't check for typos, (lack of time yet), but quite sure of it

best regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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