Set copy range from "A2" down to first completely empty row?

[klunker]

Hi All,
I have the following code I found on the web and am having a difficult time setting my copy range from "A2:first completely empty row". I've put starts next to the bit of code that calls out the copy range that I think needs to be changed. Any help would be greatly appreciated.

Please Login or Register  to view this content.

Thank you.

[protonLeah]

For starters try:

Please Login or Register  to view this content.

[klunker]

Proton,
Thanks for the reply but this copies all of the data on the Sheet("Array 8"). I guess to be more clear, I have three "chunks" of data on Array 8. The number of rows that are used will vary for each "chunk", but there is always a blank row separating them. That is why I want to stop the copy at the first blank row after A2. Eventually I will need to copy all three of the chunks but I'm trying to get the code for just the first "chunk" to start. Any thoughts? Thanks again!

[protonLeah]

Another suggestion:

Please Login or Register  to view this content.

[klunker]

Proton,
Thanks, I think that both of those solutions would work if my data wasn't so scattered among the columns and rows. Unfortunately, there are sometimes blank cells before the end of my data group.

How about searching in Column A for a term, say "Dataset2", and then copy the info above that row? So the code would search through several rows in Column A until it hits the cell that contains the value "Dataset2", say for example, cell A22. The code then copies from (A2:V22), "22" being the variable row number above the one that "Dataset2" was found in. Thanks for all of your help with this.

oh, and I had to change "Dim Topblock as Long" to "String" in order to get the above code to run. I don't know enough about VBA to explain why but it worked after I did that.

[protonLeah]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[klunker]

Thanks proton but I solved it with a .range search. I'll give you some rep as your ideas will come in handy for other code uses. The code I used is below.

Please Login or Register  to view this content.