Excel macro question -- copying an irregular block of data

Factual Questions
1

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!

2

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?

3

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.

4

If your data is contiguous (i.e., no blanks in the middle), something like this will work for copying one column at a time.


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
5

Wouldn’t that copy all 1000 rows from column A? when I only want 900?

6

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?

7

The latter – I want to copy 900 rows for all columns.

8

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.

9

Can’t say this is the most elegant way, but here’s how I do it cheap and easy.


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
10

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.

11

Actually, that is quite elegant. I would say that is the textbook method.

BTW I am a moderator at 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.

12

Then you can do this:



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
13

That worked! Thanks!