Excel VBA

  Sir Radfordin 01:18 09 Jan 2004
Locked

I need to transpose a large amount of data in an excel sheet to create a report. I've written a macro that does this but my limited VB knowledge means its very long with a step being repeated lots of time. My thinking is I should be able to loop the following bit: And increase the value of n in Range("An").Select and Range("Bn").Select by one each time, up to 130 times. Then to carry on with the rest of the macro. In the likely even that this site messes up the formatting the loop part can be found here click here (.txt file0 and the full macro found here click here (.txt file) If nothing else you can all enjoy laughing at my attempts to programe!

Sheets("qr_courseindex").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.End(xlDown).Select
Range("An").Select
ActiveSheet.Paste
Sheets("qr_courseindex").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("Bn").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A1").Select

  VoG II 07:47 09 Jan 2004

Sorry, I don't have time before going to work to give you a "full" solution.

You could use something like

For i - 1 to 130

Range("A" & i).Select

'etc

Next i

==============

However, I think that all this copying and pasting is going to slow things down. Much faster to use code like


Sheets("Sheet1").Range("A" & i).Value = Sheets("Sheet2").Range("B" &i).Value

which will "copy" one cell at a time. If you're trying to copy several columns however, your method might be best.

Also, to speed things up insert the following near the top

Application.ScreenUpdating = False

and near the bottom

Application.ScreenUpdating = True

  Sir Radfordin 08:41 09 Jan 2004

Thanks VoG was hopeing you would see this one. That seems to have solved the problem for now.

What we have is a database that spits out in ColA a subject code and in ColB a stand number. There are loads of stand numbers for each subject code and I need to create a list that shows one subject code and then all its stand numbers. As Each stand number is linked to a subject code this has proved really hard to do.

The solution I've thought up is to instert blank lines at the end of each subject group and then copy over the first subject code from ColA and then all the stand numbers from ColB transposing on paste.

It may be a dirty solution but it works, and the manual way would have involved something like 250,000 copy/paste commands! If you can think of something better that would be great. Thanks.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?