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?
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.
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)
Range(Cells(2, 1), Cells(maxRow, 8)).Copy
' add code for pasting here
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.