Macro about Transpose

jessetr

New Member
Joined
Apr 10, 2012
Messages
36
Hi all,

I have a loooong VERTICAL list of names and addresses of companies. All of the information is in one column, let's say, column A. Each company is separated by a couple of blank rows.

I can do a Transpose on Company #1 , starting at let's say A2. Maybe a total of 5 rows. It results in the entire information spread out along B2.

If I record all my actions in a macro, and then go to Company #2 , the macro will work except for one very important detail, it results in the information spread out along B2 as well. It just overwrites what was there before.

Obviously, this is useless. How do I get around this?

Thank you.

Jesse
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Post your code and maybe someone will help you modify it. If you post some sample data for more than one company, in a form that can be copied from a browser and pasted to Excel, we can see how your data are arranged,and someone may be able to help you with code that does what you want w/o having to run a macro for each company separately.
 
Upvote 0
Oh, I just created a macro using the Record button. I don't know anything about code. How would I post it? Thanks for letting me in on something new! : )
 
Upvote 0
Give this a try in a copy of your workbook.
Code:
Sub RearrangeCoData()
  Dim rA As Range
  
  Application.ScreenUpdating = False
  For Each rA In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    rA.Copy
    Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
  Next rA
  Application.CutCopyMode = False
  Application.ScreenUpdating = False
End Sub
 
Upvote 0
Ok, upon learning how to insert code, I saw that I have to choose between: Copy Excel VBA Code to a Regular Module, Copy Excel VBA Code to a Regular Module, Copy Excel VBA Code to a Worksheet Module, or Copy Excel VBA Code to a Workbook Module. Which one do I choose?

I do have many workSHEETS that this macro needs to affect, both created and to be created.

Thanks! I really appreciate your help!

J.


 
Upvote 0
The code should go in a 'Regular Module'.
The code acts on whatever is the 'active sheet' at the time it is run, so it could be applicable to any worksheet.
Post back with more details if you need further help with any of that.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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