Find methods in VBA for Excel

[jhuck]

For anyone who can help me!

I need to find rows which meet a specific criteria, for example:

Find and copy to the clip board all rows which have a blank in Collumn A, the four letter phrase "ACCT" in Column B and column C is not blank.

Thanks in advance for any help you might be able to give me!

 

[VBAjedi]

One phrase: "Advanced Filter". Read up on it in the Excel help files. You can either use it manually from Excel (easy), or automate it via VBA (harder, but certainly possible) if you do it a lot.

Post back if you get stumped on something.



VBAjedi

 

[Zathras]

A simpler way is just to work with a copy of the data and use Data/Sort:

1. Sort column "A" and then delete where not blank.
2. Sort column "B" and then delete where not "ACCT"
3. Sort column "C" and then delete where blank.
4. Select all and then copy.

 

[VBAjedi]

Zathras,

Your way certainly is a faster "quick and dirty" solution. . . guess it depends on whether this will be a one-time operation or something that gets done routinely.

jhuck?

VBAjedi

 

[Adnane]

Hi,

here's a bit of code you could start with.

Sub Row_Select()

Cells(1, 1).Select

For i = 1 To 10
Cells(i, 1).Select

If Cells(i, 1).Value = &quot;&quot; And Left(Cells(i, 2), 4) = &quot;acct&quot; And Cells(i, 3) <> &quot;&quot; Then
ActiveCell.EntireRow.Copy
End If

Next

End Sub

what's still missing is how to copy it to the clipboard and the range definition.

Hope this helps

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[VBAjedi]

Adnane,

Another nice &quot;quick-and-dirty&quot; solution.

The potential disadvantage of your approach is speed (or lack thereof). Jhuck hasn't told us how many rows of data we're working with here, but if it's a large spreadsheet looping through the rows will almost certainly be MUCH slower than using a built-in Excel function.

I always seek to use built-in functions if at ALL possible for this reason.



VBAjedi

 

[VBAjedi]

Adnane,

Another nice &quot;quick-and-dirty&quot; solution.

The potential disadvantage of your approach is speed (or lack thereof). Jhuck hasn't told us how many rows of data we're working with here, but if it's a large spreadsheet looping through the rows will almost certainly be MUCH slower than using a built-in Excel function.

For this reason, I always seek to use built-in functions if at ALL possible.



VBAjedi

 

[VBAjedi]

Dang it - I hate it when I do that! Saw an edit to make in my post right after I hit Submit but didn't hit Stop fast enough.

LOL


VBAjedi

 

[jhuck]

Thanks everyone, I unfortunately never know how many rows contain the criteria that I'm looking for. In the past it has been from several hundred to several thousand. What would be nice to have would be code that finds the first occurance and then while setting this (securing the row) as the first row, hightlite down to the last occurance of this criteria and then copy and paste. The copy and paste is easy enough, its the finding of all the adjacent rows that meet the criteria that I'm having trouble with.

john

 

[VBAjedi]

I gather from your last post this is a recurring task. I strongly suggest you write VBA to use the Advanced Filter. It's a simple matter then for your code to copy the visible rows to the clipboard, or to loop through the visible rows and perform operations on them. This will give you the flexibility to change your criteria and to adjust for data sets that change in size each time.

Post back if you need help with the VBA on this.

VBAjedi

 

[suoirotciv]

CLIPBOARD.GETTEXT

How can i check using VBA if the clipboard has a value or it has anything on it that i can paste?

Please pardon the grammar.
Not good in english.

 

[VBAjedi]

Victor,

This needs to be a new thread. Start a new thread with the subject &quot;Check Clipboard for contents?&quot;


VBAjedi

 

[suoirotciv]

hehehe . . .

atleast i have now the permission to create a new thread for my question . . .

thank you VBAjedi

may the force be with you . . .

Please pardon the grammar.
Not good in english.