Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Range After Paste Special
I am currently using the following macro to copy a selected range and
paste special (transpose) it to another area on the worksheet: Application.CutCopyMode = False Selection.Copy Range("G1").Select Do While ActiveCell.Value < "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub I would like to modify this so that the range that I copied is then deleted (cells shift up) after being pasted to the other are so that I may continue to execute this macro on another range of data. Can anyone help me build out this final piece? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Range After Paste Special
hi
you left out the good part. the range that you copied and how. to do this you simply have to declare the copied range a variable like this..... dim rng as range set rng = Range("A1:A5") ' or what ever range you copied. if you use vb to select then you can use..... set rng = selection then instead of selection.copy use rng.copy then after paste special transpose add this.... rng.clearcontents worked in my test. selection refers to what is currently selected and changes with each new selection. by declaring and using range variables like above, vb remembers them and you can return to them and perform other actions like delete. hope this helps. Post back if you have problems or questions. regards FSt1 "Uninvisible" wrote: I am currently using the following macro to copy a selected range and paste special (transpose) it to another area on the worksheet: Application.CutCopyMode = False Selection.Copy Range("G1").Select Do While ActiveCell.Value < "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub I would like to modify this so that the range that I copied is then deleted (cells shift up) after being pasted to the other are so that I may continue to execute this macro on another range of data. Can anyone help me build out this final piece? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Range After Paste Special
Thank you -- it worked perfectly. But now that I have this and am
using it, I wonder if I can automate my whole process. Essentially, I have one column of data which needs to be normalized (this was a .pdf report from a database. The db crashed and now I am trying to recreate it): A1 Name A2 Address A3 Phone A4 FIXED DATA A5 Email A6 Name A7 Address A8 Phone A9 FIXED DATA A10 Email A11 Email A12 Name A13 Address A14 Phone A15 FIXED DATA A16 Email Essentially, I have been highlighting say A1:A5 and then running the macro, then A6:A11 etc. But, highlighting each is time consuming. Is there a way to highlight the entire column and have the macro auto- select a range of fields that make a record (i.e. Name, Address, Phone, FIXED DATA, E-mail) transpose it and then do the same to the next record and the next. There are two considerations with this data set: 1) not every record has something populated in the e-mail field and 2) some records have more tha one e-mail. Now, every record has the FIXED DATA field which contains the exact same fixed value for each value. So, this might be a delimiter of a sort. The VB would have to essentially say select a range which consists of the first cell through the last cells to contain an @ immediately after the "FIXED DATA" cell (and of course, it will have to look at the cell after the cells with and @ to know that that is a new record and to not include it in the range) Once that range is auto-identified, the date would be copied, transposed and the the next range is selected etc. Any thoughts? :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Range After Paste Special
hi
i think it can be done but i do have to be somewhere at 8:00 so i wont be able to work on in now. your "considerations" are a bit confusing. post some real data then check this post later or tomorrow. i'll se what i can do. Regards FSt1 "Uninvisible" wrote: Thank you -- it worked perfectly. But now that I have this and am using it, I wonder if I can automate my whole process. Essentially, I have one column of data which needs to be normalized (this was a .pdf report from a database. The db crashed and now I am trying to recreate it): A1 Name A2 Address A3 Phone A4 FIXED DATA A5 Email A6 Name A7 Address A8 Phone A9 FIXED DATA A10 Email A11 Email A12 Name A13 Address A14 Phone A15 FIXED DATA A16 Email Essentially, I have been highlighting say A1:A5 and then running the macro, then A6:A11 etc. But, highlighting each is time consuming. Is there a way to highlight the entire column and have the macro auto- select a range of fields that make a record (i.e. Name, Address, Phone, FIXED DATA, E-mail) transpose it and then do the same to the next record and the next. There are two considerations with this data set: 1) not every record has something populated in the e-mail field and 2) some records have more tha one e-mail. Now, every record has the FIXED DATA field which contains the exact same fixed value for each value. So, this might be a delimiter of a sort. The VB would have to essentially say select a range which consists of the first cell through the last cells to contain an @ immediately after the "FIXED DATA" cell (and of course, it will have to look at the cell after the cells with and @ to know that that is a new record and to not include it in the range) Once that range is auto-identified, the date would be copied, transposed and the the next range is selected etc. Any thoughts? :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Range After Paste Special
Well, I am almost there. As a test, I have something which would
essentially take the first 10 records and transpose them and then the next 10 etc.: Dim cnt As Integer Do Set rng = Selection.Offset.Resize(10) Application.CutCopyMode = False rng.Copy Range("G" & rng.Row).Select Do While ActiveCell.Value < "" ActiveCell.Offset(1, 0).Select Loop Selection.PasteSpecial Transpose:=True rng.Delete Shift:=xlUp Range("A" & ActiveCell.Row).Select cnt = 1 Do ActiveCell.Offset(-1, 0).Select cnt = cnt + 1 Loop Until ActiveCell.Value & "" = "" If cnt < 3 Then Exit Do Else ActiveCell.Offset(1, 0).Select End If Loop End Sub The trick now is to figure out how to substitute that 10 for something which is defined by the e-mail address, as I mentioned above. Any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy range in macro using paste special values | Excel Discussion (Misc queries) | |||
Copy and Paste Special Dynamic Range | Excel Programming | |||
Context Menu, Paste Special From Selected Range | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
Select Range every 20 rows copy & paste special | Excel Programming |