Increment problem

ASP Free Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

November 3rd, 2004, 04:41 PM

mdelaney

Registered User

Join Date: Oct 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Increment problem

Hi...I've got a very large worksheet (10,000 rows) in a spreadsheet that I need to make smaller (obviously). A pull from our system at work created this Excel spreadsheet and instead of being 2000 rows, it was 10,000. It included a field that isn't needed which is why it over bloated itself. Anyways, I need to transpose the values from one column, over four columns. I have no problem getting my macro to do it to one specific range of cells, but I'm unsure of how to go about getting it to do it for the entire worksheet. I have included my code below. I started to do it manually, as my code will indicate, but wanted a better and faster way of doing this.

Code:

 
Sub MyTranspose()
'------------------ BEGIN - USER MODIFIABLE SECTION ------------------
r_first = 163 ' the row BEFORE the first row of your list
r_last = 9958 ' the last row of your list
r_step = 4	' the number of rows between the row borders
'------------------ END - USER MODIFIABLE SECTION --------------------
Dim a As Range
Set a = Selection		' remember cell selection
Cells.Select
 
For r = r_first To r_last Step r_step
Range("E164:E167").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Next r
 
If (r_last - r_first) Mod r_step <> 0 Then
r = r_last
Range("E164:E167").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End If
End Sub

November 4th, 2004, 05:07 PM

Darius

Contributing User

Join Date: Sep 2004

Posts: 108

Time spent in forums: 11 h 16 m 54 sec
Reputation Power: 4

This may help

Sub transpose()
Dim IN_RANGE As Range, OUT_RANGE_FIRST_CELL As Range, R_STEP As Integer, Linea As String
Set IN_RANGE = Range(InputBox("Input Range", "Transpose"))
Set OUT_RANGE_FIRST_CELL = Range(InputBox("Init Cell Output Range", "Transpose"))
R_STEP = InputBox("Number of Consecutive Cells", "Transpose")
For R = 0 To IN_RANGE.Rows.Count - 1 Step R_STEP
For C = 0 To R_STEP - 1
Cells(OUT_RANGE_FIRST_CELL.Row + R / R_STEP, C + OUT_RANGE_FIRST_CELL.Column) = Cells(IN_RANGE.Row + R + C, IN_RANGE.Column)
Next C
Next R
End Sub

Input Range is feeded like c10:c105
Init Cell Output Range like e10
Number of COnsecutive Cells is an Integer

Viewing: ASP Free Forums > Programming > Visual Basic Programming > Increment problem

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump