Hi,

I have a workbook with 2 sheets , the first has a table of data , six columns , 25 rows.
What i'd like to do is on a second sheet populate the six columns with only those rows
from the first sheet where column b for any of the 20 rows is populated.

I've tried look ups and a couple of other things but because i don't know which rows will be populated i don't have a starting point for my lookup.
Can anyone advise how i might be able to do what i need?

Thanks

So it doesn't work to just use a formula like this on the second worksheet?

in col A:
=if(len(Sheet1!A2)>0,A2,"")

in col B:
=Sheet1!B2

I realize this may give you blank rows on the second sheet.

Or are you looking for an automated solution to copy rows in a vba procedure?

As for formulas, someone else may be able to post with a more complex formula that actually looks up the "next row down" where col B is populated. But when formulas get that complex, I tend to use VBA instead, I find it much easier to troubleshoot, document, explain and debug.

Hi,

Thanks for the swift reply , I gave your suggestion a go and you're right it does give blank rows which doesn't really help me for what i need,
how would you suggest going about it using a vba method?
Thanks again

You could do this, aircode sorry I am unable to test at the moment, paste this into a standard module:

Sub Test()

dim myRange as Range
dim ws1 as Worksheet
dim ws2 as Worksheet
set ws1=thisworkbook.worksheets("Sheet1") 'or whatever your source sheet name is
set ws2 = thisworkbook.worksheets("Sheet2") 'or whatever your destination sheet is
lastrow=ws1.range("A" & ws1.rows.count).end(xlup).Row

For Each myRange in ws1.Range("A2:A" & lastrow) 'if no headers on source sheet, change A2 to A1

if len(myrange.offset(0,1).value)>0 then
myrange.entirerow.copy ws2.range("A" & ws2.rows.count).end(xlup).offset(1,0)
end if

Next myRange

End Sub

Disclaimer: I tend to gravitate toward loops, since the "efficiency" question isn't very relevant, I think, in cases where it's a question between code taking 1.3 seconds or 1.6 seconds. But in the interest of best practices, VBA usage of Advanced Filter might be considered even better. I just can't write aircode to do it at this stage in my learning.

you know something , for untested air code that was pretty spectacular , it works exactly like it says in the tin, thanks.

I have one last question if you'll indulge me a little longer.

Instead of copying the EntireRow , is there a way i can select specific columns only?
I tried creating a second range myRange2 to copy those cells but it didn't work at all , any advice?

thanks again

I've grown far too addicted to looping through ranges, so I do it in my sleep. Definitely not something I can brag about. LOL

So as you're looping through column A, you want to copy just specific cells? Just use Offsets. Example, let's say you only want to copy G.

Change:
myrange.entirerow.copy ws2.range("A" & ws2.rows.count).end(xlup).offset(1,0)

To:
myrange.offset(0,6).copy ws2.range("A" & ws2.rows.count).end(xlup).offset(1,0)

At some point all this use of Offset to determine where the next blank row is in the Destination sheet may become problematic IF you don't fully understand and make sure it corresponds to where you do or don't have blank data.

Because the ws2.range("A" & ws2.rows.count).end(xlup).offset(1,0) is like goint Ctrl+UpArrow from bottom of sheet to closest-up cell with data. If you have data on that destination sheet on a "bottom row" in B, C, D, E but not A, then this method will go up even higher, until it finds data in A.

In other words you have to be aware of this, hope that explains

I see what you're saying about the offset and had a little play with it.

the end game i'm trying to get to is something like this:

if column D has a value greater than 0 then copy columns A,B,C, G, I,J onto a new sheet.

do you think multiple offsets would do that?

offset(0,0).copy
offset(0,1).copy
offset(0,2).copy
offset(0,6).copy
etc...

thanks again

Yes, as long as you can answer, which column in the source sheet, (out of the ones that you're copying - a, b, c, g, i, j) will always have SOME data in them? Because they will become the rows on the destination sheet, and i want to make sure that the cell I use on the destination sheet for that .End(xlup) routine is going "up" to the right row - the truly bottom row of data.

Thanks again Isaac,

Column B will always have data , unfortunately the format (which columns follow which) can't really change otherwise i'd set the range as fixed and move all the columns together. And I can confirm that the .End(xlup) routine does work as expected.

Yes but the .end(xlup) may not always work if we're not sure that the data we're putting into the column on destination sheet (that's being.endxlup'ed) will always have data.

So ... Let's put A,B,C, G, I,J on the new sheet. And let's correspond that and use col B on destination sheet to decided where the 'next blank row' is.


I think I got that right, you can correct any Offset mathematical errors I might have done

Perfect again Isaac,
Thanks so much for all your help , i really appreciate it.

Thanks.

Glad to help!