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’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.
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?
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.
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
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.
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.
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