Macro for organizing excel points

**schalla** · 06-11-2012, 02:32 PM

Hi,

The data I’m trying to get organized into a single column is in excel rows 22-78.
0 0.04 0.08 0.12 0.16 0.2 0.24 0.28 0.32 0.36 0.4 0.44 0.48 0.52 0.56 0.6 0.64 0.68 0.72 0.76 0.8 0.84 0.88 0.92 0.96 1 1.04 1.08 1.12 1.16 1.2 1.24 1.28 1.32 1.36 1.4 1.44 1.48 1.52 1.56 1.6 1.64 1.68 1.72 1.76 1.8 1.84 1.88 1.92 1.96 2 2.04 2.08 2.12 2.16 2.2 2.24 2.28 2.32 2.36 2.4 2.44 2.48 2.52 2.56 2.6 2.64 2.68 2.72 2.76 2.8 2.84 2.88 2.92 2.96 3 3.04 3.08 3.12 3.16 3.2 3.24 3.28 3.32 3.36 3.4 3.44 3.48 3.52 3.56 3.6 3.64 3.68 3.72 3.76 3.8 3.84 3.88

This is all just one row of data.
This is the data in one row. And such I have from row 22-78. the final files have a similar number of columns but many more rows. I need to write a macro to organize this into a single column in excel which can do it for us

Thank u Sun

**WasWodge** · 06-11-2012, 04:55 PM

Try this, but I am not sure how well it will work with the amount of data you have.

Please Login or Register  to view this content.

**schalla** · 06-11-2012, 06:48 PM

Hi,

I modified the code:
Sub RowsToColumn()
Dim RN As Range
Dim RI As Range
Dim r As Long
Dim LR As Long
Application.ScreenUpdating = False
Columns(1).Insert
r = 0

LR = Range("A" & Rows.Count).End(xlUp).row
For Each RN In Range("A1:A" & LR)
r = r + 1
For Each RI In Range(RN, Range("XFD" & RN.row).End(xlToLeft))
r = r + 1
Cells(r, 1) = RI
RI.Clear
Next RI
Next RN
Columns("A:A").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

This works for a single row.
Now I want to make this work for rows A22-78.
Bascially, I think i need a loop
something like this
Set rng = Range("A1:A56")
For Each row In rng.Rows
Columns(1).Insert
r = 0
LR = Range("A" & Rows.Count).End(xlUp).row
LR = Range("A" & Rows.Count).End(xlUp).row
For Each RN In Range("A1:A" & LR)
....
But not sure abt that Columns(1).Insert
bcos

**WasWodge** · 06-11-2012, 07:00 PM

Sorry I am a bit puzzled by your last post, the code I posted works from A22 to last row with data. I just retried on a sheet with data from row 22 to 256 and across to column N and it put all the data in column A, starting at A1.

P.S. My code should also have ended "Application.ScreenUpdating = True".

Please Login or Register  to view this content.

**schalla** · 06-11-2012, 09:42 PM

Copy of Oak_E1_S1_01.xlsx

In this attachment, Suppose A22, all the column in A22 must become(transpose) one column-say column1
A23- column2

I dont think the code u posted is working this way.

This is my very first macro in excel vba

Really appreciated ur help
Sun

**WasWodge** · 06-11-2012, 09:53 PM

No,you are correct it doesn't work that way. I took

I need to write a macro to organize this into a single column

literally. I am just about to get some much needed sleep but I will have another look at it tomorrow evening if no-one else has come up with any ideas.

**schalla** · 06-11-2012, 10:06 PM

Apologize for the confusion

**WasWodge** · 06-12-2012, 02:35 PM

OK, before do anything with this can you just clarify what you want and what I can see?

1) Are you saying that what you are asking for, is to take the data from raw data rows 22 onwards and transpose it in the same location i.e row 22 becomes column A starting at row22, row 23 becomes column B starting at row 22, row 24 becomes column C starting at row 22 e.t.c.

2) then is sheet Oak Flats E11, cell B23 idirectly references raw data cell A22 and so on for the rest of the cells in raw data

Is this correct?
(Probably more questions to follow)

**schalla** · 06-12-2012, 03:00 PM

Hi, Thank u so for much. I just checked with the person who needs this task. He wants everything in one column.
I am only working with the Rawdata sheet. So after A22 in one column, A23 starts below it- all in one column. So we don't have to change anything.. A22 has 458 cells. In the final output we will have 458*(78-22+1) number of rows in one column

In the code you gave me, I should be selecting the rows A22-78, copy it? and then run the macro correct?. Feel free to correct me.
Instead can the macro automatically select rows starting from row22 to the #of rows in the sheet...can v use rowcount?

Also, A22-78 has a total of Count:26016. When I run the macro it is only showing me 16453 cells in one column.

Thanku

**WasWodge** · 06-12-2012, 03:27 PM

Originally Posted by schalla

Hi,
On last question. In the code you gave me, I should be selecting the rows and then running the macro. Instead can the macro automatically select rows starting from row22 to the #of rowcount.
Thanku

No you shouldn't be selecting anything the macro already runs from Row 22 to the last used row.That is what this row says

Please Login or Register  to view this content.

. It says for each row number starting at row 22 till Last row in column B.

**schalla** · 06-12-2012, 03:39 PM

Shouldn't it be A22:A instead of B22: B?
rowA22 should become column1 and then A23 adds on

If I don't select anything on the spreadsheet, nothing happens. Should I be adding a select(A22:all rows in the range) statement

Thank u
sun

**WasWodge** · 06-12-2012, 03:53 PM

No it should be column B as we have inserted a blank column

Please Login or Register  to view this content.

for the data to be transposed to. Selecting the cells makes no difference one way or another to whether the code runs. Afraid I don't know any reason why it wouldn't run without seeing the workbook.
If the code ran with you selecting it then it would have run without you selecting it.

P.S. I the code again on your data in post #5 and it created 26106 entries

**schalla** · 06-12-2012, 04:28 PM

Here's the excel macro sheet with the macro I am running.

It is working only if I select rows and then do Ctrl C.

Thank u
Sun

**WasWodge** · 06-12-2012, 04:42 PM

Thats not the macro I posted there was no

Please Login or Register  to view this content.

in my code

Please Login or Register  to view this content.

When you added the sheet then the code was working on a blank sheet. The code was

Please Login or Register  to view this content.

**schalla** · 06-12-2012, 04:52 PM

Ok Correct. I added that line. Add sheet. Would it be too much of a change to have the new data in another sheet.
Also,
trying to understand ur code.
what is For Each RI In Range(RN, Range("XFD" & RN.Row).End(xlToLeft))

**WasWodge** · 06-12-2012, 05:52 PM

Try

Please Login or Register  to view this content.

and to answer your question;
For Each RI In Range means for every cell in range. RI in this case stands for Row Item but could be called anything
(RN, Range("XFD" & RN.Row).End(xlToLeft)) = RN is Column B row number, XFD is the last column in Excel 2007/10. Basically on each row you are looking at the last cell in the row and then stepping right to left until the last cell with data is found(.End(xlToLeft).

**schalla** · 06-12-2012, 06:34 PM

Hi,

I haven't tried this new code yet.
Tried running the earlier code with around 700 rows. But it crashes. can v do anything abt it?

Sun

**WasWodge** · 06-12-2012, 06:46 PM

Not much I can do about it I'm afraid, it must be your system resources. I just ran the code on 1033 rows x 111 columns of data and it ran ok (took a little while)

**WasWodge** · 06-12-2012, 07:17 PM

Try this as it might be better if you are struggling with resources, just one warning though you can't have blank cells in the middle of your rows.
Please note also that I didn't write it, I just found it in my old codes.

Please Login or Register  to view this content.

Macro for organizing excel points

LinkBack

Thread Tools

Rate This Thread

Display

Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Re: Macro for organizing excel points

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions