Macro recording

  • Thread starter Thread starter Pilgrim
  • Start date Start date
P

Pilgrim

I understand the concept of macros, I just don't know how they execute and
the possible functions.
I would like to import a considerable amount of data from excel into access.
The problem I have is that the information I want to import for each record
is on two adjacent rows. I need a macro that will move the data contained
in just one cell to the end of the row above it and then delete the row
that, that information was contained in. Then I need it to repeat that
function on every other row. Sounds easy right? Way my luck runs you could
do anything you want to do except what I need. Anyone know how to do this?
Or direct me to a site I could research it to do it on my own.

Mike
 
Yeah it can be done by formulas, filters, and
copy/pastes. It is a bit technical and should be done off
this post -- particularly if it's a recurring theme that
needs a macro. It can be done with indirects or offset
formulas. You'll want to use a clone of your original
sheet tho'.

Manually, step 1 would be something like this:
=IF(ISODD(ROW()),INDIRECT(ADDRESS(ROW()+1,1)),"")

Excel Support Technician
www.canhelpyou.com
-----------------------------------------------------------
-----------
Every member of the CanHelpYou team holds at least a
Microsoft Level 1 Excel certification. In addition, we
have experts in PowerPoint, Word and other products such
as IIS, SQL Server 2000, network connectivity. We're here
to help whenever you need us.
 
Hi
This depends very much how your sheets is laid out.

This macro takes the value of the cell below the active cell, copie
into the cell to the right of the active cell, then deletes the ro
beneath the activecell:

Sub move_data_once()
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 0)
ActiveCell.Offset(1, 0).EntireRow.Delete
End Sub

This macro keeps repeating the same action until it encounters a blan
cell. If all of your data are in a single column, there are no blan
rows between your data, and there are always exactly two rows that yo
want to trsnspose into a singe row, it might work:

Sub move_all_data()
Do While ActiveCell.Value <> ""
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 0)
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Loop
End Su
 
Back
Top