Delete a Range After Paste Special

[Uninvisible]

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?

 

[Guest]

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]

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?

 

[Guest]

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]

Thank you for following up. Essentially, I converted a PDF to excel
and all of the data looks as follows:

A1 Erica Smith
A2 Vice President
A3 Company, Inc.
A4 710 Bridgeport Avenue
A5 Southfield, MI 48076
A6 (555)555-5555
A7 (555)555-5555
A8 E-Mail Address Associated Contact Created By Date
A9 (e-mail address removed) EGOKCE 03/08/06
A10 Darby Smith
A11 Vice President
A12 Company, Inc.
A13 710 Bridgeport Avenue
A14 Southfield, MI 48076
A15 (555)555-5555
A16 E-Mail Address Associated Contact Created By Date
A17 (e-mail address removed) CATALOGS 03/08/06
A18 (e-mail address removed) HINGERMA 03/22/06
A19 Carol Smith
A20 Vice President
A21 Company, Inc.
A22 710 Bridgeport Avenue
A23 Southfield, MI 48076
A24 (555)555-5555
A25 E-Mail Address Associated Contact Created By Date

Basically, I used the above macro to copy cells A1:A9 and then
transpose them to another part of the worksheet so I could normalize
the data and recreate the database. There are over 30,000 records, so
to do this all day is time consuming. What I am interested in doing is
revising the code above to include a loop which would run down the
entire A column, auto select a range, copy it, paste it, delete it and
then move down to select the next range until all records have been
transposed. Every range of cells has the following data element in
it: E-Mail Address Associated Contact Created By Date (this was a
column header on the pdf report). Following that there is a cell with
an email address. After the e-mail addresses, a new record starts.
In some instances, a record may have more than one e-mail (see
A17:A18) and in others, none at all (A25). Since the copied range is
deleted after it is pasted, the idea would be for the VB to start the
range with the first cell in Column A that has data in it and close it
using the e-mail address. The related e-mail address could be found
by using the @ as the key character as well as a condition indicating
that the first cell after the E-Mail Address Associated Contact
Created By Date that does not have an @ would be the start of the next
record. Thus, the range would now be identified and then copied,
transposed and then deleted. The loop would continue until all
records are transposed. As I am a novice with VB, taking a while to
figure out how to do this...

 

[Uninvisible]

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?