# Excel macro question -- copying an irregular block of data **URL:** \ **Category:** Factual Questions\ **Created:** [December 16, 2015, 8:00pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628 "2015-12-16T20:00:00Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 16, 2015, 8:00pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/1 "2015-12-16T20:00:00Z")
I’ve had excellent results in the past, coming here for “how do I do this…” questions on Excel VBA. Hope you guys can come through one more time. I’m creating a macro that will copy a block of data from tab A to tab B. The problem is that the data on tab A is irregular. Let’s say there is data in columns A - H; but A-D have 1000 rows, and E - H have 900 rows. When I run the macro, I don’t know how many rows will be in E-H (but I know it will be \< 1000). I want to select everything from A2 - H900. thanks in advance! ---
**Author:** ![Quercus](https://avatars.discourse-cdn.com/v4/letter/q/7ab992/32.png) [@Quercus](https://boards.straightdope.com/u/Quercus)\ **Post date:** [December 16, 2015, 8:07pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/2 "2015-12-16T20:07:10Z")
I’m not sure what you’re trying to do. If columns A to D have data in 1000 rows, and E to H have data in 900 rows and blanks for 100 rows below that, why not just copy A1:H100 ? You’ll copy the blanks, but so what? ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 16, 2015, 8:11pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/3 "2015-12-16T20:11:45Z")
> [@Quercus](#): > > I’m not sure what you’re trying to do. If columns A to D have data in 1000 rows, and E to H have data in 900 rows and blanks for 100 rows below that, why not just copy A1:H100 ? You’ll copy the blanks, but so what? I’m copying a daily update into a cumulative data source for a pivot table. Just trying to keep the data clean, and not cluttered up with hundreds of blank rows. Which wouldn’t be the end of the world, but it offends my sense of elegant system design. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [December 16, 2015, 8:31pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/4 "2015-12-16T20:31:27Z")
If your data is contiguous (i.e., no blanks in the middle), something like this will work for copying one column at a time. ```auto Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ``` ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 16, 2015, 9:05pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/5 "2015-12-16T21:05:28Z")
> [@TroutMan](#): > > If your data is contiguous (i.e., no blanks in the middle), something like this will work for copying one column at a time. > > ```auto > > Range("A1").Select > Range(Selection, Selection.End(xlDown)).Select > Selection.Copy > > ``` Wouldn’t that copy all 1000 rows from column A? when I only want 900? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [December 16, 2015, 9:51pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/6 "2015-12-16T21:51:13Z")
That will select all cells going down until it encounters the first blank. Try selecting the top row of a column, then press ctrl-shift-down arrow. This code does the same thing. ETA: or maybe I’m not understanding you. If you have 1000 rows in column A and 900 rows in column B, do you want to copy 1000 in A and 900 in B? Or do want to ignore the last 100 rows in A? ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 16, 2015, 9:55pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/7 "2015-12-16T21:55:37Z")
> [@TroutMan](#): > > That will select all cells going down until it encounters the first blank. > > Try selecting the top row of a column, then press ctrl-shift-down arrow. This code does the same thing. > > ETA: or maybe I’m not understanding you. If you have 1000 rows in column A and 900 rows in column B, do you want to copy 1000 in A and 900 in B? Or do want to ignore the last 100 rows in A? The latter – I want to copy 900 rows for all columns. ---
**Author:** ![mcgato](https://avatars.discourse-cdn.com/v4/letter/m/ac8455/32.png) [@mcgato](https://boards.straightdope.com/u/mcgato)\ **Post date:** [December 16, 2015, 10:17pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/8 "2015-12-16T22:17:14Z")
You can go column by column and find the minimum column length. Then copy the appropriate block. I used to find maximum column length the brute force way. I would make a do…while loop, advancing the row number each loop, and stop when I hit a blank cell. Nest that in a for…next loop across the columns, and mark the minimum length. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [December 16, 2015, 10:37pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/9 "2015-12-16T22:37:31Z")
Can’t say this is the most elegant way, but here’s how I do it cheap and easy. ```auto Sub CopyMyRows() Dim i As Long, maxRow As Long maxRow = 1000 For i = 1 To 8 ' assuming you are looking at cols A-H maxRow = WorksheetFunction.Min(maxRow, Cells(Rows.Count, i).End(xlUp).Row) Next Range(Cells(2, 1), Cells(maxRow, 8)).Copy ' add code for pasting here End Sub ``` ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 16, 2015, 10:40pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/10 "2015-12-16T22:40:12Z")
Actually, I think I can simplify and leverage that – I know that column H is always going to be the shortest column. So I just have to get maxrow in H. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [December 17, 2015, 4:12am UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/11 "2015-12-17T04:12:30Z")
> [@TroutMan](#): > > Can’t say this is the most elegant way, but here’s how I do it cheap and easy. Actually, that is quite elegant. I would say that is the textbook method. BTW I am a moderator at [ExcelForum.com](http://ExcelForum.com), a free forum for Excel (and other Office applications) questions. Free, though registration is required. I have seen a lot of Excel threads on SDMB with good answers but that site specializes in it and has lots of experts. It also has the tremendous advantage of allowing you to attach your file for analysis/update. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [December 17, 2015, 4:14am UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/12 "2015-12-17T04:14:22Z")
> [@jsc1953](#): > > Actually, I think I can simplify and leverage that – I know that column H is always going to be the shortest column. So I just have to get maxrow in H. Then you can do this: ```auto Sub CopyMyRows() Dim maxRow As Long maxRow = Cells(Rows.Count, "H").End(xlUp).Row Range(Cells(2, 1), Cells(maxRow, 8)).Copy ' add code for pasting here End Sub ``` ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 17, 2015, 4:15pm UTC](https://boards.straightdope.com/t/excel-macro-question-copying-an-irregular-block-of-data/740628/13 "2015-12-17T16:15:05Z")
That worked! Thanks!