Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to columns
Hi, I have a list of 500 items in column A. I want to copy the first 50
to column B1:B50, the next 50 to C1:C50 etc. Can someone show me how to do this with a simple macro? Chuck |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to columns
Sub Test()
Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Step 50 Cells(i, "A").Resize(50).Copy Cells(1, i \ 50 + 2) Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hi, I have a list of 500 items in column A. I want to copy the first 50 to column B1:B50, the next 50 to C1:C50 etc. Can someone show me how to do this with a simple macro? Chuck |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to columns
Bob Phillips wrote: Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Step 50 Cells(i, "A").Resize(50).Copy Cells(1, i \ 50 + 2) Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hi, I have a list of 500 items in column A. I want to copy the first 50 to column B1:B50, the next 50 to C1:C50 etc. Can someone show me how to do this with a simple macro? Chuck Thanks, everything worked fine, you really solved my problem I know next to nothing about macros or VB,but hope to learn something for future use. I have a few questions: 1. After getting your post, I started to record a macro, stoped after one entry and erased the code and replaced it with yours. I came up with errors such as "Compile error expected End Sub ". then I noticed that there was another macro there "test" which I assumed came from the Subtest() in your macro. I ran the test macro and everything worked great. What is the best way to copy code into new macro? I went to Record new macro but did not know what choices to make. 2.I'm trying to understand the macro as well as use it. About iLastRow = Cells(Rows.Count, "A"). Is Rows.Count a VB term or is the in Excel like Counta or Countif I tried to look up resizing but could not find anything in my excell book Any help in explaning how the macro works would be appreciated. I'm in my 70's and this comes really hard. But I do want to learn more. Thanks, Chuck |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to columns
wrote in message oups.com... Thanks, everything worked fine, you really solved my problem I know next to nothing about macros or VB,but hope to learn something for future use. I have a few questions: 1. After getting your post, I started to record a macro, stoped after one entry and erased the code and replaced it with yours. I came up with errors such as "Compile error expected End Sub ". then I noticed that there was another macro there "test" which I assumed came from the Subtest() in your macro. I ran the test macro and everything worked great. What is the best way to copy code into new macro? I went to Record new macro but did not know what choices to make. Sounds like you messed up copying the code in. You can actually record an empty macro, just stop the recorder immediately, before doing anything. Personally, I go to the VBIDE (Alt-F11), and insert a module (InsertModule), and enter the code directly. What does it look like now, after your updates? 2.I'm trying to understand the macro as well as use it. About iLastRow = Cells(Rows.Count, "A"). Is Rows.Count a VB term or is the in Excel like Counta or Countif It is Excel VBA constant, effectively created by Excel. It holds the number of rows in the sheet. I tried to look up resizing but could not find anything in my excell book Resize does exactl;y what it says on the can, it changes the size. The syntax is expression.Resize(RowSize, ColumnSize) expression refers to the thing being resized, a range in reality, rows and columns refers to the size that therange being resized will be resized. VBA Help says Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Any help in explaning how the macro works would be appreciated. I'm in my 70's It is very simple,. It first determines where the last row is. It then steps through the rows 50 rows at a time. It copies the current row, resized by 50 rows, to the first row in the next column, which is calculated using the current row number, diving by 50, taking the integer part of that division (i.e. 1\50 = 0, 51\50 =1) and adding 2. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying to columns
Bob Phillips wrote: wrote in message oups.com... Thanks, everything worked fine, you really solved my problem I know next to nothing about macros or VB,but hope to learn something for future use. I have a few questions: 1. After getting your post, I started to record a macro, stoped after one entry and erased the code and replaced it with yours. I came up with errors such as "Compile error expected End Sub ". then I noticed that there was another macro there "test" which I assumed came from the Subtest() in your macro. I ran the test macro and everything worked great. What is the best way to copy code into new macro? I went to Record new macro but did not know what choices to make. Sounds like you messed up copying the code in. You can actually record an empty macro, just stop the recorder immediately, before doing anything. Personally, I go to the VBIDE (Alt-F11), and insert a module (InsertModule), and enter the code directly. What does it look like now, after your updates? 2.I'm trying to understand the macro as well as use it. About iLastRow = Cells(Rows.Count, "A"). Is Rows.Count a VB term or is the in Excel like Counta or Countif It is Excel VBA constant, effectively created by Excel. It holds the number of rows in the sheet. I tried to look up resizing but could not find anything in my excell book Resize does exactl;y what it says on the can, it changes the size. The syntax is expression.Resize(RowSize, ColumnSize) expression refers to the thing being resized, a range in reality, rows and columns refers to the size that therange being resized will be resized. VBA Help says Resizes the specified range. Returns a Range object that represents the resized range. expression.Resize(RowSize, ColumnSize) expression Required. An expression that returns a Range object to be resized. RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same. ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same. Any help in explaning how the macro works would be appreciated. I'm in my 70's It is very simple,. It first determines where the last row is. It then steps through the rows 50 rows at a time. It copies the current row, resized by 50 rows, to the first row in the next column, which is calculated using the current row number, diving by 50, taking the integer part of that division (i.e. 1\50 = 0, 51\50 =1) and adding 2. Bob, Thank you very much for taking the time to explain. Chuck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging two columns and keeping the data from both | Excel Worksheet Functions | |||
wanting to know width of several columns for copying | Excel Discussion (Misc queries) | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |